Collection functions

Work with lists as values.

Syntax

Write the function's name and the list, with the value or second list it asks for.

FIRST(list)
LAST(list)
NTH(list, index)
POSITION(list, value)
LENGTH(list)
REVERSE(list)
APPEND(list1, list2)
SLICE(list, start, [length])
PUSH(list, value)
FLATTEN(list)
UNION_DISTINCT(list1, list2)
INTERSECTION(list1, list2)
MINUS(list1, list2)

Parameters and arguments

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

  • FIRST

    list

    Required: Yes
  • LAST

    list

    Required: Yes
  • NTH

    list

    Required: Yes
  • index

    Required: Yes
  • POSITION

    list

    Required: Yes
  • value

    Required: Yes
  • LENGTH

    list

    Required: Yes
  • REVERSE

    list

    Required: Yes
  • APPEND

    list1

    Required: Yes
  • list2

    Required: Yes
  • SLICE

    list

    Required: Yes
  • start

    Required: Yes
  • length

    Required: No
  • PUSH

    list

    Required: Yes
  • value

    Required: Yes
  • FLATTEN

    list

    Required: Yes
  • UNION_DISTINCT

    list1

    Required: Yes
  • list2

    Required: Yes
  • INTERSECTION

    list1

    Required: Yes
  • list2

    Required: Yes
  • MINUS

    list1

    Required: Yes
  • list2

    Required: Yes

Examples

A customer's tags held once, then counted, read by position and searched.

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
FIRSTlistA list holding the first itemFIRST([3, 1, 2])
LASTlistA list holding the last itemLAST([3, 1, 2])
NTHthe itemThe item at that positionNTH([10, 20, 30], 1)
POSITIONwhole numberThe position of the value, counting from 0, or -1 when it is not in the listPOSITION([10, 20, 30], 20)
LENGTHwhole numberHow many itemsLENGTH([1, 2, 3])
REVERSElistThe list in reverse orderREVERSE([1, 2, 3])
APPENDlistThe two lists joined into oneAPPEND([1, 2], [3, 4])
SLICElistA part of the listSLICE([10, 20, 30, 40], 1, 2)
PUSHlistThe list with the item at the endPUSH([1, 2], 3)
FLATTENlistOne list with the items of each inner list, one level deepFLATTEN([[1, 2], [3]])
UNION_DISTINCTlistThe first list, then the items of the second that are not already in it. Coming: a repeat inside the first list is kept: UNION_DISTINCT([1, 1, 2], [2, 3, 3]) answers [1,1,2,3]UNION_DISTINCT([1, 2], [2, 3])
INTERSECTIONlistThe items that are in both listsINTERSECTION([1, 2, 3], [2, 3, 4])
MINUSlistThe items of the first list that are not in the secondMINUS([1, 2, 3], [2])
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 missing, such as a list with no value to addGive the list and the value
HTQL-2001NTH was given a position past the end of the listUse a position from 0 to one less than the length
HTQL-5005NTH was given a negative positionUse a position of 0 or more
Limitations
  • Positions in NTH and POSITION count from 0.
  • FIRST and LAST return a list holding the one item. Use NTH for the item itself.
  • These functions work on lists. LENGTH also counts the characters of text; a number or null is refused with HTQL-2001.
  • Coming: a variable that holds a list of records, written bare as LET rows = [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}], is read apart at the commas inside the records: LENGTH(rows) answers 4, and NTH(rows, 0) answers {"sku": "A1. Put the list inside a record, as in LET order = {"rows": […]}, and read it as order.rows.
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 LENGTH to count the rows a LOOKUP kept in a variable.
  • Use UNION_DISTINCT to merge two lists without repeats.