Parsing and JSON functions

Applies to VAST Cluster 5.5.

Reading, checking, converting and building JSON text.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Check and inspect

Function	What it does
json_array_length	Count the elements of a JSON array, or of an array at a path; anything that isn't an array counts 0.
json_exists	Check whether a path exists in a JSON document, including keys whose value is JSON null.
json_keys	List the keys of a JSON object, or of an object at a path, in document order.
json_type	Return the type of a JSON document, or of the value at a path: OBJECT, ARRAY, VARCHAR, a number type, BOOLEAN or NULL.
json_valid	Check whether text is valid JSON, without failing on invalid input.
json_contains	Test whether a JSON document contains a value, an object's key/value pairs or an array's elements anywhere inside it.
json_structure	Describe the shape of a JSON document, with a SQL type name for each value; the result can be used as the structure argument of json_transform.

Read values

Function	What it does
json_extract_string (also `json_extract_path_text`, `->>`)	Read the value at a path in a JSON document as plain text; json_extract_path_text and the ->> operator are the same function.
json_extract (also `json_extract_path`, `->`)	Read the value at a path in a JSON document, as JSON text; json_extract_path and the -> operator are the same function.
json_value	Read the scalar value at a path in a JSON document, as JSON text; objects and arrays return NULL.

Build JSON

Function	What it does
array_to_json	Convert a list to a JSON array; any other argument type fails.
json_quote	Convert a SQL value to JSON text, quoting and escaping strings; it gives the same results as to_json.
row_to_json	Convert a struct to a JSON object, with the field names as keys; any other argument type fails.
to_json	Convert any SQL value (a number, string, date, list, struct or map) to JSON text.
json_array	Build a JSON array from a list of values of any type, with how each type is written and how NULLs become null.
json_merge_patch	Apply JSON merge patches to a document: add, replace and remove keys, merge nested objects, and what NULLs and non-object documents do.
json_object	Build a JSON object from key-value pairs, with how keys and values are written, NULL keys and values, and the errors.
json_pretty	Write a JSON document over several lines, indented by four spaces, with the result type, NULLs and the errors for invalid JSON.
json	Parse JSON text and write it back in compact form: what changes, how numbers and escapes are written, and the errors for invalid JSON.

Convert to SQL types

Function	What it does
json_transform_strict (also `from_json_strict`)	Convert a JSON document to a struct, list or value of SQL types, failing on a missing key or a value that doesn't convert; from_json_strict is the same function.
json_transform (also `from_json`)	Convert a JSON document to a struct, list or value of SQL types in one pass, with NULL for fields that are missing or don't convert; from_json is the same function.

SQL as JSON

Function	What it does
json_deserialize_sql	Turn a JSON syntax tree from json_serialize_sql back into SQL text, how the text is normalised, and the errors.
json_serialize_sql	Parse the text of a SELECT statement into its syntax tree as JSON, with the options, the error object and how to read fields from the result.