Identifiers

Name tables, columns and variables the way the data names them, and HTQL finds them.

Syntax

An identifier starts with a letter or an underscore, and continues with letters, digits and underscores. A dot joins parts.

table_name
alias.column
variable.field.nested_field
variable.list.0.field
variable.list.field
variable.list.value
_leading_underscore

Parameters and arguments

  • Table

    Letters, digits and underscores, in GET, LOOKUP, LEARN and FOR. LOOKUP also accepts it in quotes, in any case

  • Alias

    A short name given to a GET's table

  • Column

    The field's name as the answer names it, in camelCase

  • Alias column

    The alias, a dot and the column

  • Record field

    The variable, a dot and the field

  • Nested field

    Dots continue into deeper records and JSON columns

  • Item by position

    A whole number after a list is a position, counting from 0

  • Field of every item

    A field name after a list reads that field from every item

  • Item by value

    A value the plain list holds is that item; one it does not hold is null

  • Variable

    A letter or underscore, then letters, digits and underscores

Case matters in four places. Keywords such as GET, FILTER and RETURN are written in any case. A table name is read in any case in LOOKUP, and in capitals it is refused in GET. A variable must be spelled exactly. A column is found by its name in any case: contentType, ContentType and CONTENTTYPE are one field, so write it as the answer names it.

Examples

Fields written as the answer names them, and the names the result uses.

Return type and behaviour

Write a field in a statement by the name the answer gives it, in camelCase: FILTER(e.contentType = "navLink") reads the field that comes back as contentType. Every name in a result is camelCase, and a name you choose in RETURN or AS is converted the same way, so "orderTotal" comes back as orderTotal and "kind_of_link" as kindOfLink. A name without an underscore comes back as you wrote it.

A path that reaches nothing returns null: a field the record does not have, a position past the end of a list, or a value the list does not hold. A field read across a list returns the values found, in order. A list with one match returns that one value.

Money, percentages, ratios and decimals

A statement always speaks in the units people use, and HTQL converts at the edge. A money field is written in major units (12.34), a percentage as a plain percentage (5 is five percent) and a ratio as a plain decimal (0.25). The database stores each as a whole number so that it never drifts: the engine's own documented examples turn 12.34 into 1234 for a currency with two decimal places, and 5 percent into 500 basis points. You never write those stored integers, and the answer never shows them.

KindYou writeThe answer returnsHow it is stored
MoneyMajor units, as in FILTER(l.lineTotal > 50)A record {"amount": …, "currency": …}A whole number of the currency's smallest unit, rounded half away from zero when a literal has more places than the currency
PercentageA plain percentage, 25 for 25 percentA plain number in percentA whole number of basis points
RatioA plain decimal, 0.25A plain decimal, never roundedA whole number scaled by 10^16
Plain decimalA decimal such as 0.1The full floating point valueA floating point number

A currency's number of decimal places comes from the business's currency settings: 0 for JPY, KRW and VND, 2 for AUD, USD, EUR and GBP, and 3 for KWD, BHD and OMR. A currency with no minor unit has no decimal point in the stored whole number and none in the amount.

On the way out, each money value becomes {"amount": …, "currency": …}. The amount is a plain number in major units, not text and not rounded. The currency is the one named by a currency field in the same record, and the business's own currency when the record has none. When that field is in the record it is consumed into every amount, so it does not appear a second time beside them. A percentage is a plain number, and a ratio a plain decimal. A stored value that cannot be read as a whole number answers null with a notice in the response, never a silent zero.

A computed value keeps what it measures. The average of a money column is money, and the average of a percentage column is a percentage, whatever key you give the answer, and the average keeps its fraction instead of being forced to a whole smallest unit. Arithmetic on plain numbers stays floating point and returns its full value.

Note

Write major units and plain percentages. Never write a stored integer such as cents or basis points, and never divide or multiply by 100 yourself: the conversion is HTQL's, so a hand written scale is applied twice.

Tip

Never round for display. Return the full value and let the screen choose the places to show. A rounded answer cannot be added up later without losing the pennies the rounding threw away.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-2001A field name does not exist on the table, or two fields would answer to itUse the field's name as the answer names it, or LIST TABLES to see the fields
HTQL-1004A GET table is quotedWrite the name without quotes
HTQL-3003A table name is not one HTQL serves, or a GET table is written in capitalsCheck the name, and write it in lower case
HTQL-1003A variable name starts with a digit or holds a hyphenStart with a letter or an underscore, and use underscores
Limitations
  • Not supported: a quoted table name in GET, such as GET("entries"). Write GET(entries, e).
  • Not supported: a hyphen in a name, such as my-var or a path step like fields.name.en-US. Name variables with underscores, and read the whole field, e.fields.name, when a key holds a hyphen.
  • A name read before any LET has bound it is null. LET binds in order, from the top of the statement.
Best practices
  • Write the same camelCase names in statements that you read in results.
  • Give an alias to a table you read with GET and RETURN.
  • Choose a result name in camelCase, so it comes back as you wrote it.
  • Reach into JSON with a path instead of fetching the whole document and walking it in code.
  • Write money in major units and percentages as plain percentages; return values whole, never rounded or scaled for display.
  • Return a row's currency beside its amounts when rows can differ, so each amount names its own currency.