JSON
Work with JSON as you already know it.
Syntax
Records use braces and quoted names, and lists use square brackets. A dot reads a field, a position or a value.
{"name": value, "other": {"nested": 1}}
[value, value, 1 + 1]
variable.field
variable.list.0
variable.list.field
alias.column.fieldParameters and arguments
RecordQuoted names with any value, nested as deep as you need. A name written twice keeps its last value
ListValues of any mix of types, including records, other lists and computations such as
[1 + 1, 4]Field accessA dot after a variable reads that field of the record, as deep as it goes
Column accessA dot after an alias column reads inside a JSON column, such as
e.fields.titleItem by positionA whole number after a list:
order.lines.0.street. It counts from 0, and a position past the end is nullField of every itemA field name after a list reads that field from every item:
order.lines.streetItem by valueAfter a list of plain values, a value the list holds is that item:
user.tags.vip
Examples
A record held in a variable, with two fields read from it.
Return type and behaviour
A JSON column comes back as JSON, nested as it is stored. Reading e.fields.title returns the value stored there, which can itself be a record. A field the record does not have is null.
A record with a single field keeps its whole value: LET doc = {"a": [1, 2]} and RETURN {"doc": doc} answers {"doc": {"a": [1, 2]}}.
A LOOKUP held in a variable is a list of records. LENGTH counts it, and a field name after it reads that field from every row.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 | A record is not written in HTQL's grammar, for example an unquoted name | Quote every name |
HTQL-4012 | A bare list was returned | Return it inside a record |
HTQL-4006 | A name in a returned record has a space or another character that is not a letter, digit, dot or underscore | Rename it |
Limitations
- Names in a record are quoted text. In a RETURN they use letters, digits, dots and underscores.
- Not supported: a list written directly after RETURN. Return it inside a record:
RETURN {"items": [1, 2]}. - Not supported: a hyphen in a path step, such as
e.fields.name.en-US. Reade.fields.nameand take the locale in your code, or pick the field with a function.
Best practices
- Quote every name in a record.
- Read only the fields you need, with a dot.
- Build the shape your code expects in RETURN, so no reshaping is needed afterwards.
- Prefer a path such as
order.lines.streetto fetching the document and looping in code.