Document manipulation functions

Reshape a record without touching the table it came from.

Syntax

Write the function's name and the records or template.

MERGE(record1, record2)
UNSET(record, keys)
KEEP(record, keys)
FLATTEN(list)
HANDLEBARS(template, data, [mode])

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • MERGE

    record1

    Required: Yes
  • record2

    Required: Yes
  • UNSET

    record

    Required: Yes
  • keys

    Required: Yes
  • KEEP

    record

    Required: Yes
  • keys

    Required: Yes
  • FLATTEN

    list

    Required: Yes
  • HANDLEBARS

    template

    Required: Yes
  • data

    Required: Yes
  • mode

    Required: No

Examples

A customer record laid over a record of defaults: the customer's own values win, and the defaults fill what is missing.

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
MERGErecordOne record with the fields of both. Where both have a field, the second record's value is keptMERGE({"a": 1}, {"b": 2})
UNSETrecordThe record without those keysUNSET({"a": 1, "b": 2}, "a")
KEEPrecordThe record with only those keysKEEP({"a": 1, "b": 2}, "a")
FLATTENlistOne list with the items of each inner list, one level deepFLATTEN([[1, 2], [3]])
HANDLEBARStextThe template with each name filled inHANDLEBARS("Hi {{name}}", {"name": "Ada"})
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 both records, or the record and the keys
Limitations
  • These functions return a new value. They do not change a table: use UPDATE for that.
  • Coming: a variable that holds a record with a single field, passed to HANDLEBARS, fills nothing: after LET order = {"name": "Ada"}, HANDLEBARS("Hi {{name}}", order) answers "Hi ". A record with two or more fields, or one written in the call, works.
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 MERGE to add defaults to a record.
  • Use UNSET to drop fields before you return a record to a caller.