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.field

Parameters and arguments

  • Record

    Quoted names with any value, nested as deep as you need. A name written twice keeps its last value

  • List

    Values of any mix of types, including records, other lists and computations such as [1 + 1, 4]

  • Field access

    A dot after a variable reads that field of the record, as deep as it goes

  • Column access

    A dot after an alias column reads inside a JSON column, such as e.fields.title

  • Item by position

    A whole number after a list: order.lines.0.street. It counts from 0, and a position past the end is null

  • Field of every item

    A field name after a list reads that field from every item: order.lines.street

  • Item by value

    After 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
ErrorWhat happenedHow to recover
HTQL-1003A record is not written in HTQL's grammar, for example an unquoted nameQuote every name
HTQL-4012A bare list was returnedReturn it inside a record
HTQL-4006A name in a returned record has a space or another character that is not a letter, digit, dot or underscoreRename 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. Read e.fields.name and 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.street to fetching the document and looping in code.