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

    value

    Required: Yes
  • IS_ARRAY

    value

    Required: Yes
  • VALUES

    record

    Required: Yes
  • PLUCK

    records

    Required: Yes
  • key

    Required: Yes
  • HAS

    record

    Required: Yes
  • key

    Required: Yes
  • ZIP

    keys

    Required: Yes
  • values

    Required: Yes
  • NOT_NULL

    values

    Required: Yes

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.

FunctionReturnsBehaviourForm
IS_OBJECTtrue or falseTrue when the value is a recordIS_OBJECT({"a": 1})
IS_ARRAYtrue or falseTrue when the value is a listIS_ARRAY([1])
VALUESlistThe record's valuesVALUES({"a": 1, "b": 2})
PLUCKlistThat field from every recordPLUCK([{"a": 1}, {"a": 2}], "a")
HAStrue or falseTrue when the record has that fieldHAS({"a": 1}, "a")
ZIPrecordA record made from the two listsZIP(["a", "b"], [1, 2])
NOT_NULLthe first value that is not nullThe first value that is not nullNOT_NULL(null, null, 5)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5004An argument is missingGive the records and the key
Limitations
  • PLUCK returns numbers as decimals, and skips a record that lacks the field.
  • IS_OBJECT and IS_ARRAY are 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 from order.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.