JSON functions
Look inside records and lists of records.
Syntax
Write the function's name and the record or list.
IS_OBJECT(value)
IS_ARRAY(value)
VALUES(record)
PLUCK(records, key)
HAS(record, key)
ZIP(keys, values)
NOT_NULL(values)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
IS_OBJECT
Required: YesvalueIS_ARRAY
Required: YesvalueVALUES
Required: YesrecordPLUCK
Required: Yesrecords
Required: YeskeyHAS
Required: Yesrecord
Required: YeskeyZIP
Required: Yeskeys
Required: YesvaluesNOT_NULL
Required: Yesvalues
Examples
An order's lines held as a list of records: the SKUs, the units and the highest price, each taken from the lines with PLUCK.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
IS_OBJECT | true or false | True when the value is a record | IS_OBJECT({"a": 1}) |
IS_ARRAY | true or false | True when the value is a list | IS_ARRAY([1]) |
VALUES | list | The record's values | VALUES({"a": 1, "b": 2}) |
PLUCK | list | That field from every record | PLUCK([{"a": 1}, {"a": 2}], "a") |
HAS | true or false | True when the record has that field | HAS({"a": 1}, "a") |
ZIP | record | A record made from the two lists | ZIP(["a", "b"], [1, 2]) |
NOT_NULL | the first value that is not null | The first value that is not null | NOT_NULL(null, null, 5) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5004 | An argument is missing | Give the records and the key |
Limitations
PLUCKreturns numbers as decimals, and skips a record that lacks the field.IS_OBJECTandIS_ARRAYare also listed under Type checking.- Coming: a variable that holds a list of records, written bare, is read apart at the commas inside the records: after
LET rows = [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}],PLUCK(rows, "sku")answers an empty list. Put the list inside a record,LET order = {"rows": […]}, and pluck fromorder.rows, as the examples do.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use PLUCK to turn a list of records into a list of one field, ready for SUM or AVERAGE.
- Test with IS_OBJECT before you read fields from a value that may not be a record.