# JSON functions

> 80 JSON functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

80 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

| Function | Summary |
| --- | --- |
| [`json_all_paths`](#json_all_paths) | Lists every logical document leaf, including empty containers and unindexed fields. |
| [`json_all_paths_with_types`](#json_all_paths_with_types) | Pairs each terminal location in a document with its native JSON type label. |
| [`json_array_agg`](#json_array_agg) | Collects grouped SQL values into an ordered JSON sequence, retaining null inputs. |
| [`json_array_agg_strict`](#json_array_agg_strict) | Builds a document list from nonmissing group values while preserving explicit JSON null. |
| [`json_array_all`](#json_array_all) | Requires unanimous predicate success across document-array members, with empty input yielding true. |
| [`json_array_any`](#json_array_any) | Reports whether at least one whole JSON element passes a correlated condition. |
| [`json_array_append`](#json_array_append) | Adds supplied JSON values after the final element without flattening nested lists. |
| [`json_array_contains`](#json_array_contains) | Checks exact whole-element membership within a selected native JSON array. |
| [`json_array_elements`](#json_array_elements) | Expands a native JSON list into one relational row per element in source order. |
| [`json_array_elements_text`](#json_array_elements_text) | Emits array entries as unquoted strings or serialized values in separate output rows. |
| [`json_array_filter`](#json_array_filter) | Keeps document-array elements selected by a Boolean lambda, preserving their order. |
| [`json_array_find`](#json_array_find) | Returns the earliest complete JSON element that passes a predicate, or SQL NULL. |
| [`json_array_length`](#json_array_length) | Counts top-level slots in a JSON list and rejects other document shapes. |
| [`json_array_map`](#json_array_map) | Transforms a JSON list element by element while keeping its original cardinality. |
| [`json_array_reduce`](#json_array_reduce) | Folds document elements from left to right into an explicitly initialized accumulator. |
| [`json_build_array`](#json_build_array) | Constructs a heterogeneous JSON list directly from zero to sixteen SQL arguments. |
| [`json_build_object`](#json_build_object) | Creates a JSON object from alternating text keys and independently typed values. |
| [`json_concat`](#json_concat) | Combines two documents using shallow object union or JSON array concatenation. |
| [`json_contains`](#json_contains) | Tests recursive structural inclusion of a candidate subtree within a JSON document. |
| [`json_distinct_paths`](#json_distinct_paths) | Inventories unique leaf addresses across grouped documents in lexical order. |
| [`json_distinct_paths_and_types`](#json_distinct_paths_and_types) | Maps discovered field locations to their observed JSON kinds over a document group. |
| [`json_each`](#json_each) | Produces key/value rows from a native object, retaining each field’s JSON representation. |
| [`json_each_text`](#json_each_text) | Exports object fields as relational key/text pairs with SQL NULL for null-valued members. |
| [`json_exists`](#json_exists) | Reports whether a JSONPath selects any item, including a present null-valued field. |
| [`json_exists_all`](#json_exists_all) | Requires every supplied document address to resolve, with an empty collection passing. |
| [`json_exists_any`](#json_exists_any) | Checks for at least one resolvable address among multiple document paths. |
| [`json_exists_tz`](#json_exists_tz) | Tests document-path presence with explicit timezone rules for temporal conversions. |
| [`json_extract`](#json_extract) | Retrieves a JSON subtree or converts a path selection to a requested SQL type. |
| [`json_extract_array`](#json_extract_array) | Converts a selected document list into a SQL array with nullable, checked elements. |
| [`json_extract_array_raw`](#json_extract_array_raw) | Serializes each selected list element separately as canonical JSON text. |
| [`json_extract_bool`](#json_extract_bool) | Reads a Boolean JSON component using literal keys and one-based array offsets. |
| [`json_extract_float`](#json_extract_float) | Obtains a checked Float64 reading from a component-selected JSON number. |
| [`json_extract_int`](#json_extract_int) | Decodes a signed 64-bit integer at a literal document component path. |
| [`json_extract_keys_and_values`](#json_extract_keys_and_values) | Returns sorted object-key pairs with each value converted to a chosen SQL descriptor. |
| [`json_extract_keys_and_values_raw`](#json_extract_keys_and_values_raw) | Exports object entries as lexically ordered keys paired with serialized JSON fragments. |
| [`json_extract_path`](#json_extract_path) | Follows literal key components and zero-based subscripts to a native JSON subtree. |
| [`json_extract_path_text`](#json_extract_path_text) | Renders a component-addressed document value as unquoted or canonical textual content. |
| [`json_extract_raw`](#json_extract_raw) | Returns one canonical JSON fragment, retaining string quotes and the null literal. |
| [`json_extract_string`](#json_extract_string) | Unquotes an actual JSON string reached through literal key or positional components. |
| [`json_extract_uint`](#json_extract_uint) | Reads a nonnegative 64-bit count from a selected JSON numeric component. |
| [`json_from_value`](#json_from_value) | Converts typed SQL data into native JSON without reparsing strings as documents. |
| [`json_get`](#json_get) | Selects one object member or zero-based array slot while retaining its JSON type. |
| [`json_get_text`](#json_get_text) | Extracts a direct document field as SQL text, mapping a JSON null to SQL NULL. |
| [`json_has`](#json_has) | Checks component-path presence using one-based indexing without inspecting the stored value. |
| [`json_hash`](#json_hash) | Produces a seeded 64-bit SipHash digest of canonical logical document content. |
| [`json_hash_extended`](#json_hash_extended) | Computes a 128-bit document fingerprint with versioned SipHash and an optional seed. |
| [`json_insert`](#json_insert) | Inserts an absent member or shifts array elements around a specified insertion position. |
| [`json_key`](#json_key) | Returns the object key at a one-based position in canonical lexical ordering. |
| [`json_keys`](#json_keys) | Collects a selected object’s field names into a sorted SQL string array. |
| [`json_length`](#json_length) | Counts object fields or array members at the root or a chosen document path. |
| [`json_match`](#json_match) | Evaluates a declarative JSON document predicate with correlated element matching. |
| [`json_merge`](#json_merge) | Combines object fields left to right, keeping the final value for each overlapping key. |
| [`json_merge_patch`](#json_merge_patch) | Applies RFC 7396 patches that recursively update objects and delete null-valued members. |
| [`json_object_agg`](#json_object_agg) | Assembles a keyed JSON document from grouped name/value pairs, retaining SQL null values. |
| [`json_object_agg_strict`](#json_object_agg_strict) | Combines nonnull group entries into an object whose surviving keys must be unique. |
| [`json_object_keys`](#json_object_keys) | Yields one sorted object-member name per relational row. |
| [`json_parse`](#json_parse) | Parses UTF-8 text or bytes into a canonical JSON value with exact decimal numbers. |
| [`json_parse_as`](#json_parse_as) | Decodes an entire JSON value into a constant SQL target type using checked conversion. |
| [`json_path_each`](#json_path_each) | Streams a finite row sequence of native values selected by JSONPath from each input document. |
| [`json_path_each_tz`](#json_path_each_tz) | Expands timezone-aware path matches into event-local result rows. |
| [`json_path_match`](#json_path_match) | Evaluates a JSONPath predicate as true, false, or unknown instead of testing presence. |
| [`json_path_match_tz`](#json_path_match_tz) | Resolves temporal JSONPath conditions under a caller-supplied timezone. |
| [`json_path_query`](#json_path_query) | Collects every JSONPath match into one native array in deterministic traversal order. |
| [`json_path_query_first`](#json_path_query_first) | Selects the earliest item yielded by deterministic JSONPath traversal. |
| [`json_path_query_first_tz`](#json_path_query_first_tz) | Finds the first path-selected document item using explicit temporal interpretation. |
| [`json_path_query_tz`](#json_path_query_tz) | Builds a native match list from JSONPath evaluation with required timezone control. |
| [`json_pretty`](#json_pretty) | Formats a native document with sorted keys, LF line breaks, and selectable indentation. |
| [`json_project`](#json_project) | Selects or excludes document fields according to a literal or dotted-path projection. |
| [`json_query`](#json_query) | Returns SQL/JSON query results with explicit wrapper, scalar, empty, and error policies. |
| [`json_remove`](#json_remove) | Deletes singular document paths in order and compacts affected array positions. |
| [`json_replace`](#json_replace) | Updates an existing JSON leaf while leaving absent destinations unchanged. |
| [`json_set`](#json_set) | Assigns a JSON leaf and optionally creates missing object ancestors or appends an array slot. |
| [`json_set_lax`](#json_set_lax) | Updates a document with a chosen policy for an SQL NULL replacement value. |
| [`json_strip_nulls`](#json_strip_nulls) | Recursively removes null-valued object members, optionally compacting null array slots. |
| [`json_to_string`](#json_to_string) | Serializes typed SQL data as canonical JSON text with quoted strings and exact decimals. |
| [`json_type`](#json_type) | Identifies a selected native value as object, array, string, number, boolean, or null. |
| [`json_unwind`](#json_unwind) | Copies a document once per selected array element, replacing that field and adding a zero-based ordinal. |
| [`json_valid`](#json_valid) | Checks JSON syntax, UTF-8 encoding, and object-key uniqueness without returning a document. |
| [`json_value`](#json_value) | Extracts one scalar through SQL/JSON rules, returning unquoted text or a chosen SQL type. |
| [`json_values`](#json_values) | Collects scalar path matches into a SQL array while retaining nullable result positions. |

## json_all_paths

Lists every logical document leaf, including empty containers and unindexed fields.

```text
json_all_paths(document:json) -> nullable(array(string))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `nullable(array(string))` |
| Aliases | `JSONAllPaths` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns sorted canonical leaf paths, representing array positions explicitly and treating empty containers as terminal leaves. SQL NULL input returns SQL NULL, whereas a JSON-null leaf remains discoverable. Traversal includes unindexed content and raises RESOURCE_LIMIT if per-value depth, byte, or expression limits are exceeded.

**Example**

```sql
json_all_paths(json_parse('{"sensor":{"value":4}}'))
-- → ['$.sensor.value']
```

## json_all_paths_with_types

Pairs each terminal location in a document with its native JSON type label.

```text
json_all_paths_with_types(document:json) -> nullable(map(string,string))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `nullable(map(string,string))` |
| Aliases | `JSONAllPathsWithTypes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Produces a SQL map from canonical leaf paths to JSON type names, including heterogeneous array slots and empty-container leaves. A SQL NULL document yields SQL NULL, while explicit JSON null is recorded with the null label. Discovery examines logical contents independently of indexes; RESOURCE_LIMIT reports an oversized traversal or result.

**Example**

```sql
json_all_paths_with_types(json_parse('{"active":true}'))
-- → {'$.active':'boolean'}
```

## json_array_agg

Collects grouped SQL values into an ordered JSON sequence, retaining null inputs.

```text
json_array_agg(value:T) -> nullable(json)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `nullable(json)` |
| Aliases | `json_agg`, `JSON_ARRAYAGG`, `jsonb_agg` |
| Streaming | Stateful — keeps per-group state. State: unbounded |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | SQL NULL input contributes JSON null; empty group yields SQL NULL |

Collects every input as a native JSON array element, including SQL NULL as JSON null; an empty group yields SQL NULL. Use ORDER BY with a total tie-breaker to fix element order. Streaming groups require retention or a finite state budget, and exceeding that budget raises RESOURCE_LIMIT.

**Example**

```sql
(SELECT json_array_agg(v ORDER BY v) FROM (VALUES (1), (2)) AS t(v))
-- → [1,2]
```

## json_array_agg_strict

Builds a document list from nonmissing group values while preserving explicit JSON null.

```text
json_array_agg_strict(value:T) -> nullable(json)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `nullable(json)` |
| Aliases | `json_agg_strict`, `jsonb_agg_strict` |
| Streaming | Stateful — keeps per-group state. State: unbounded |
| Determinism | Order-dependent — the result depends on input order. |
| NULL handling | NULL inputs are skipped. |

Skips SQL NULL rows while retaining explicit JSON-null elements, and returns SQL NULL for an empty or entirely skipped group. A total ORDER BY determines the resulting array order. Accumulated streaming values require an admitted window or state budget; excessive state raises RESOURCE_LIMIT.

**Example**

```sql
(
  SELECT json_array_agg_strict(v ORDER BY v)
  FROM (VALUES (1), (NULL), (2)) AS t(v)
)
-- → [1,2]
```

## json_array_all

Requires unanimous predicate success across document-array members, with empty input yielding true.

```text
json_array_all(document:json,predicate:lambda(json)->nullable(bool)) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+lambda` |
| Returns | `nullable(bool)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Evaluates a pure bounded predicate against each whole JSON element and returns true only when all results are true, including for an empty array. A SQL NULL predicate result counts as false; a SQL NULL document propagates. Nonarray input raises TYPE_MISMATCH and expression-budget excess raises RESOURCE_LIMIT.

**Example**

```sql
json_array_all(json_parse('[1,2]'), x -> json_type(x) = 'number')
-- → true
```

## json_array_any

Reports whether at least one whole JSON element passes a correlated condition.

```text
json_array_any(document:json,predicate:lambda(json)->nullable(bool)) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+lambda` |
| Returns | `nullable(bool)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns true as soon as an element predicate is true, keeping all tests inside that predicate correlated to the same element. Empty arrays and predicates that are only false or SQL NULL produce false; SQL NULL input produces SQL NULL. A nonarray document raises TYPE_MISMATCH, and predicate work is subject to RESOURCE_LIMIT.

**Example**

```sql
json_array_any(json_parse('[1,null]'), x -> json_type(x) = 'null')
-- → true
```

## json_array_append

Adds supplied JSON values after the final element without flattening nested lists.

```text
json_array_append(document:json,value:json,...) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Appends one to fifteen native JSON values in argument order, preserving a nested array as one element. Any SQL NULL argument propagates SQL NULL, while explicit JSON null is appended as a stored null element. The target must be an array or TYPE_MISMATCH is raised; oversized output raises RESOURCE_LIMIT.

**Example**

```sql
json_array_append(json_parse('[1]'), json_parse('[2,3]'))
-- → [1,[2,3]]
```

## json_array_contains

Checks exact whole-element membership within a selected native JSON array.

```text
json_array_contains(document:json,value:json) -> nullable(bool)
json_array_contains(document:json,path:string,value:json) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `nullable(bool)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Compares the candidate against complete elements using canonical object keys and exact numeric equality, without flattening nested arrays or objects. A missing or nonarray selection returns false; SQL NULL arguments return SQL NULL, and JSON null can match a JSON-null element. Invalid path syntax raises INVALID_JSON_PATH.

**Example**

```sql
json_array_contains(json_parse('[1,{"a":2}]'), json_parse('{"a":2}'))
-- → true
```

## json_array_elements

Expands a native JSON list into one relational row per element in source order.

```text
json_array_elements(document:json) -> table(tuple(value:json))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/unary` |
| Returns | `table(tuple(value:json))` |
| Aliases | `jsonb_array_elements` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; JSON-null elements remain JSON null |

Emits each native array element unchanged, including a JSON-null row for a JSON-null element; WITH ORDINALITY may add a one-based ordinal. SQL NULL input emits zero rows and a nonarray raises TYPE_MISMATCH. Lateral expansion preserves the source event context without cross-row state, with RESOURCE_LIMIT bounding per-document fan-out.

**Example**

```sql
SELECT *
FROM json_array_elements(json_parse('[1,null]'))
-- → [(1),(json null)]
```

## json_array_elements_text

Emits array entries as unquoted strings or serialized values in separate output rows.

```text
json_array_elements_text(document:json) -> table(tuple(value:nullable(string)))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/unary` |
| Returns | `table(tuple(value:nullable(string)))` |
| Aliases | `jsonb_array_elements_text` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; JSON-null elements emit SQL NULL |

Expands each array slot in order, unquoting strings and serializing other nonnull values as canonical text; a JSON-null slot becomes SQL NULL. SQL NULL input emits no rows and nonarray input raises TYPE_MISMATCH. Optional ordinality is one-based, and stateless lateral expansion obeys per-input fan-out and byte RESOURCE_LIMIT.

**Example**

```sql
SELECT *
FROM json_array_elements_text(json_parse('[1,"x",null]'))
-- → [('1'),('x'),(NULL)]
```

## json_array_filter

Keeps document-array elements selected by a Boolean lambda, preserving their order.

```text
json_array_filter(document:json,predicate:lambda(json)->nullable(bool)) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+lambda` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Retains each whole element only when its predicate is true, so false and SQL NULL predicate results remove that element without changing retained values. SQL NULL input propagates and an empty array remains empty. Applying the function to another JSON kind raises TYPE_MISMATCH; bounded lambda execution can raise RESOURCE_LIMIT.

**Example**

```sql
json_array_filter(json_parse('[1,null,2]'), x -> json_type(x) = 'number')
-- → [1,2]
```

## json_array_find

Returns the earliest complete JSON element that passes a predicate, or SQL NULL.

```text
json_array_find(document:json,predicate:lambda(json)->nullable(bool)) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+lambda` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Searches the native array in input order and returns the first element whose predicate is true. A matched JSON null remains JSON null, while no match or a SQL NULL document yields SQL NULL. Nonarray input raises TYPE_MISMATCH and the predicate must fit the per-expression RESOURCE_LIMIT.

**Example**

```sql
json_array_find(json_parse('[1,2]'), x -> json_extract_int(x) = 2)
-- → 2
```

## json_array_length

Counts top-level slots in a JSON list and rejects other document shapes.

```text
json_array_length(document:json|string) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `nullable(uint64)` |
| Aliases | `jsonb_array_length` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the number of array elements, counting nested containers and JSON null as one slot each; an empty array gives zero. SQL NULL returns SQL NULL, but a nonarray JSON value raises TYPE_MISMATCH rather than returning a container-neutral count. Text input is parsed strictly and malformed JSON raises INVALID_JSON.

**Example**

```sql
json_array_length('[1,null,[2,3]]')
-- → 3
```

## json_array_map

Transforms a JSON list element by element while keeping its original cardinality.

```text
json_array_map(document:json,transform:lambda(json)->nullable(json)) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+lambda` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies a pure scalar transform to each element in source order and embeds a SQL NULL transform result as JSON null. SQL NULL documents propagate, and an empty array stays empty. The input must be a native array or TYPE_MISMATCH is raised; output and lambda work obey RESOURCE_LIMIT.

**Example**

```sql
json_array_map(
  json_parse('[1,2]'),
  x -> json_from_value(json_extract_int(x) + 1)
)
-- → [2,3]
```

## json_array_reduce

Folds document elements from left to right into an explicitly initialized accumulator.

```text
json_array_reduce(document:json,initial:json,reducer:lambda(json,json)->nullable(json)) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+lambda` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Passes the accumulator and next array element to the reducer in source order, returning the initial JSON value for an empty array. A SQL NULL reducer result becomes JSON null, whereas a SQL NULL document or initial argument propagates SQL NULL. Nonarray input raises TYPE_MISMATCH and excessive reducer work raises RESOURCE_LIMIT.

**Example**

```sql
json_array_reduce(
  json_parse('[1,2]'),
  json_parse('0'),
  (a, x) -> json_from_value(json_extract_int(a) + json_extract_int(x))
)
-- → 3
```

## json_build_array

Constructs a heterogeneous JSON list directly from zero to sixteen SQL arguments.

```text
json_build_array() -> json
json_build_array(value1:T1,value2:T2,...) -> json
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `json` |
| Aliases | `JSON_ARRAY`, `jsonb_build_array` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL argument embeds JSON null unless explicit ABSENT ON NULL; empty input builds \[] |

Converts argument positions independently, quoting SQL strings and embedding existing JSON subtrees structurally; zero arguments produce an empty array. SQL NULL arguments become JSON null unless an explicit ABSENT ON NULL clause omits them. Nonfinite numeric values raise INVALID_ARGUMENT and depth or output-byte excess raises RESOURCE_LIMIT.

**Example**

```sql
json_build_array(1, 'sensor', NULL)
-- → [1,"sensor",null]
```

## json_build_object

Creates a JSON object from alternating text keys and independently typed values.

```text
json_build_object() -> json
json_build_object(key1:string,value1:T1,key2:string,value2:T2,...) -> json
json_build_object(pairs:array(array(string))) -> json
json_build_object(keys:array(string),values:array(string)) -> json
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `json` |
| Aliases | `JSON_OBJECT`, `jsonb_build_object`, `jsonb_object` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL value embeds JSON null unless ABSENT ON NULL; SQL NULL key errors; duplicate key errors |

Accepts up to eight key/value pairs, or explicit string-array adapters, and returns an empty object for zero arguments. SQL NULL values become JSON null unless ABSENT ON NULL is requested; SQL NULL or duplicate keys raise INVALID_ARGUMENT. Strings are quoted, JSON subtrees remain structural, and invalid pair shapes raise TYPE_MISMATCH.

**Example**

```sql
json_build_object('reading', 4, 'unit', 'C')
-- → {"reading":4,"unit":"C"}
```

## json_concat

Combines two documents using shallow object union or JSON array concatenation.

```text
json_concat(left:json,right:json) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Merges two objects with right-hand values replacing overlapping keys, concatenates arrays, and wraps other operands as single-element arrays before concatenation. SQL NULL propagates; JSON null is a concrete operand and is never interpreted as a deletion command. Nested objects are not recursively merged, and an oversized combined value raises RESOURCE_LIMIT.

**Example**

```sql
json_concat(json_parse('[1]'), json_parse('2'))
-- → [1,2]
```

## json_contains

Tests recursive structural inclusion of a candidate subtree within a JSON document.

```text
json_contains(document:json,candidate:json) -> nullable(bool)
json_contains(document:json,candidate:json,path:string) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `nullable(bool)` |
| Aliases | `<@`, `@>` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Requires every candidate object field to be contained and uses set-style array membership, so repeated candidate elements do not demand repeated matches. Scalar types compare exactly, empty containers require the same container kind, and JSON null contains only JSON null. A missing selected path returns false, SQL NULL propagates, and malformed paths raise INVALID_JSON_PATH.

**Example**

```sql
json_contains(json_parse('{"a":1,"b":2}'), json_parse('{"a":1}'))
-- → true
```

## json_distinct_paths

Inventories unique leaf addresses across grouped documents in lexical order.

```text
json_distinct_paths(document:json) -> array(string)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `array(string)` |
| Aliases | `distinct_json_paths`, `distinctJSONPaths` |
| Streaming | Stateful — keeps per-group state. State: unbounded |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

Unions logical leaf paths across the group, including explicit array positions and JSON-null leaves, then sorts the canonical paths. SQL NULL documents are skipped and an empty group returns an empty SQL array. This growing schema inventory needs bounded streaming retention and raises RESOURCE_LIMIT when its state budget is exhausted.

**Example**

```sql
json_distinct_paths(json_parse('{"reading":3}'))
-- → ['$.reading']
```

## json_distinct_paths_and_types

Maps discovered field locations to their observed JSON kinds over a document group.

```text
json_distinct_paths_and_types(document:json) -> map(string,array(string))
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary` |
| Returns | `map(string,array(string))` |
| Aliases | `distinct_json_paths_and_types`, `distinctJSONPathsAndTypes` |
| Streaming | Stateful — keeps per-group state. State: unbounded |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

For each logical leaf path, collects sorted distinct JSON type labels across input documents; an absent occurrence contributes no artificial null type. SQL NULL rows are ignored, explicit JSON null contributes its own type, and an empty group produces an empty map. Streaming path/type discovery must have a state budget, with RESOURCE_LIMIT on excess.

**Example**

```sql
json_distinct_paths_and_types(json_parse('{"reading":3}'))
-- → {'$.reading':['number']}
```

## json_each

Produces key/value rows from a native object, retaining each field’s JSON representation.

```text
json_each(document:json) -> table(tuple(key:string,value:json))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/unary` |
| Returns | `table(tuple(key:string,value:json))` |
| Aliases | `jsonb_each` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; JSON-null field values remain JSON null |

Visits object fields in canonical key order and emits the key together with its native JSON value, preserving explicit JSON null. SQL NULL input emits zero rows, while a nonobject raises TYPE_MISMATCH. Each document expands independently in its source event context; RESOURCE_LIMIT bounds output cardinality and bytes.

**Example**

```sql
SELECT *
FROM json_each(json_parse('{"a":1,"b":null}'))
-- → [('a',1),('b',json null)]
```

## json_each_text

Exports object fields as relational key/text pairs with SQL NULL for null-valued members.

```text
json_each_text(document:json) -> table(tuple(key:string,value:nullable(string)))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/unary` |
| Returns | `table(tuple(key:string,value:nullable(string)))` |
| Aliases | `jsonb_each_text` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; JSON-null values emit SQL NULL value columns |

Unquotes each object string value and serializes other nonnull values as canonical text, returning fields in canonical key order. JSON-null values produce SQL NULL value columns, SQL NULL input produces no rows, and nonobjects raise TYPE_MISMATCH. Expansion is finite for each event and raises RESOURCE_LIMIT if its row or byte allowance is exceeded.

**Example**

```sql
SELECT *
FROM json_each_text(json_parse('{"a":1,"b":null}'))
-- → [('a','1'),('b',NULL)]
```

## json_exists

Reports whether a JSONPath selects any item, including a present null-valued field.

```text
json_exists(document:json|string,path:string) -> nullable(bool)
json_exists(document:json|string,path:string,variables:json) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `nullable(bool)` |
| Aliases | `@?`, `json_path_exists`, `jsonb_path_exists` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL yields SQL NULL; missing path false; present JSON null true; malformed JSON/path follows explicit ON ERROR policy |

Tests whether path evaluation produces a nonempty sequence, without converting the selected value to Boolean. SQL NULL arguments return SQL NULL, a missing path returns false, and a present JSON null returns true. INVALID_JSON or INVALID_JSON_PATH is raised by default, with explicit SQL/JSON ON ERROR clauses allowing false, true, or unknown instead.

**Example**

```sql
json_exists('{"reading":null}', '$.reading')
-- → true
```

## json_exists_all

Requires every supplied document address to resolve, with an empty collection passing.

```text
json_exists_all(document:json|string,paths:json) -> nullable(bool)
json_exists_all(document:json|string,path:string,...) -> nullable(bool)
json_exists_all(document:json|string,paths:array(string)) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(bool)` |
| Aliases | `jsonb_exists_all` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Accepts a JSON path-string array, up to fifteen variadic paths, or an explicit SQL-array bridge and verifies every path independently. JSON-null selections count as present, no paths yields true, and SQL NULL propagates. INVALID_JSON_PATH reports malformed paths; the PostgreSQL key-array adapter uses literal top-level keys rather than parsing those keys as paths.

**Example**

```sql
json_exists_all('{"a":1,"b":null}', '$.a', '$.b')
-- → true
```

## json_exists_any

Checks for at least one resolvable address among multiple document paths.

```text
json_exists_any(document:json|string,paths:json) -> nullable(bool)
json_exists_any(document:json|string,path:string,...) -> nullable(bool)
json_exists_any(document:json|string,paths:array(string)) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(bool)` |
| Aliases | `jsonb_exists_any` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Evaluates a collection of JSONPath strings and succeeds when any path produces an item, including JSON null. An empty collection returns false and SQL NULL input returns SQL NULL. Bad path syntax raises INVALID_JSON_PATH; accepted key-array compatibility calls preserve literal-key interpretation through their adapter.

**Example**

```sql
json_exists_any('{"a":1}', '$.b', '$.a')
-- → true
```

## json_exists_tz

Tests document-path presence with explicit timezone rules for temporal conversions.

```text
json_exists_tz(document:json|string,path:string,timezone:string) -> nullable(bool)
json_exists_tz(document:json|string,path:string,variables:json,timezone:string) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary+timezone` |
| Returns | `nullable(bool)` |
| Aliases | `json_path_exists_tz`, `jsonb_path_exists_tz` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; base path empty/unknown policy applies; invalid timezone or temporal conversion errors |

Evaluates existence using the required timezone for path date/time casts, never an ambient session timezone. Missing selections are false, selected JSON null is present, and SQL NULL arguments propagate. Invalid zones or rejected local-time gaps and overlaps raise INVALID_ARGUMENT; malformed input or paths raise INVALID_JSON or INVALID_JSON_PATH.

**Example**

```sql
json_exists_tz('{"reading":1}', '$.reading', 'UTC')
-- → true
```

## json_extract

Retrieves a JSON subtree or converts a path selection to a requested SQL type.

```text
json_extract(document:json|string,path:string) -> nullable(json)
json_extract(document:json|string,path:string,target:type_literal(T)) -> nullable(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `nullable(json), or nullable(T) with explicit target` |
| Aliases | `JSONExtract` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL or missing selection yields SQL NULL; typed mismatch/overflow yields SQL NULL; untyped JSON null stays JSON null |

The two-argument form returns a native subtree; a constant target descriptor requests checked typed extraction instead. Missing selections and SQL NULL yield SQL NULL, untyped JSON null stays JSON null, and typed mismatch or overflow yields SQL NULL. Malformed text raises INVALID_JSON unless an explicit compatibility null-on-error mode is selected; bad path syntax raises INVALID_JSON_PATH.

**Example**

```sql
json_extract('{"reading":4}', '$.reading')
-- → 4
```

## json_extract_array

Converts a selected document list into a SQL array with nullable, checked elements.

```text
json_extract_array(document:json|string,path:string,target:type_literal(T)) -> nullable(array(nullable(T)))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+generic` |
| Returns | `nullable(array(nullable(T)))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing/nonarray selection or invalid text yields SQL NULL; invalid individual elements yield SQL NULL elements |

Uses a constant target element descriptor to convert each selected JSON-array position independently, preserving order and null slots. SQL NULL, invalid text, missing paths, and nonarray selections return SQL NULL, while an incompatible element becomes a SQL NULL element. Descriptor errors raise TYPE_MISMATCH and resource failures still propagate as RESOURCE_LIMIT.

**Example**

```sql
json_extract_array('[1,"x",null]', '$', INT64)
-- → [1,NULL,NULL]
```

## json_extract_array_raw

Serializes each selected list element separately as canonical JSON text.

```text
json_extract_array_raw(document:json|string) -> nullable(array(string))
json_extract_array_raw(document:json|string,path_part:string|int64,...) -> nullable(array(string))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(array(string))` |
| Aliases | `JSONExtractArrayRaw` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns a SQL array of canonical element strings, retaining quotes around JSON strings and rendering JSON null as the text null. Missing or nonarray selections give an empty array, SQL NULL propagates, and malformed text raises INVALID_JSON. Component subscripts are one-based, with at most fifteen path components.

**Example**

```sql
json_extract_array_raw('[1,"x",null]')
-- → ['1','"x"','null']
```

## json_extract_bool

Reads a Boolean JSON component using literal keys and one-based array offsets.

```text
json_extract_bool(document:json|string) -> nullable(bool)
json_extract_bool(document:json|string,path_part:string|int64,...) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(bool)` |
| Aliases | `JSONExtractBool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing path, JSON null, invalid text, type mismatch or overflow yields SQL NULL |

Returns a checked Boolean from the whole document or component-selected value, with negative array offsets counting backward from the end. SQL NULL, absent components, JSON null, malformed input, and incompatible values yield SQL NULL rather than false. At most fifteen components are accepted; resource exhaustion remains RESOURCE_LIMIT.

**Example**

```sql
json_extract_bool('{"active":true}', 'active')
-- → true
```

## json_extract_float

Obtains a checked Float64 reading from a component-selected JSON number.

```text
json_extract_float(document:json|string) -> nullable(float64)
json_extract_float(document:json|string,path_part:string|int64,...) -> nullable(float64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(float64)` |
| Aliases | `JSONExtractFloat` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing path, JSON null, invalid text, type mismatch or overflow yields SQL NULL |

Converts a selected JSON numeric value to Float64 under checked conversion, rejecting overflow or lossy conversion instead of silently truncating. SQL NULL, malformed text, JSON null, missing selection, and conversion failure produce SQL NULL. Literal object keys and one-based or negative-from-end array components select the value; RESOURCE_LIMIT is not suppressed.

**Example**

```sql
json_extract_float('{"reading":1.5}', 'reading')
-- → 1.5
```

## json_extract_int

Decodes a signed 64-bit integer at a literal document component path.

```text
json_extract_int(document:json|string) -> nullable(int64)
json_extract_int(document:json|string,path_part:string|int64,...) -> nullable(int64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(int64)` |
| Aliases | `JSONExtractInt` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing path, JSON null, invalid text, type mismatch or overflow yields SQL NULL |

Extracts an Int64 with checked numeric conversion; fractional loss or out-of-range values return SQL NULL. Missing components, malformed text, JSON null, and SQL NULL input also return SQL NULL. Array components count from one, negative positions count from the end, and execution-limit failures raise RESOURCE_LIMIT.

**Example**

```sql
json_extract_int('{"reading":-4}', 'reading')
-- → -4
```

## json_extract_keys_and_values

Returns sorted object-key pairs with each value converted to a chosen SQL descriptor.

```text
json_extract_keys_and_values(document:json|string,target:type_literal(T)) -> nullable(array(tuple(string,nullable(T))))
json_extract_keys_and_values(document:json|string,path:string,target:type_literal(T)) -> nullable(array(tuple(string,nullable(T))))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `nullable(array(tuple(string,nullable(T))))` |
| Aliases | `JSONExtractKeysAndValues` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; missing/nonobject yields empty SQL array; invalid text errors; typed value conversion failures become NULL |

Builds a SQL array of key/value tuples sorted by canonical key order, checking each conversion independently so incompatible values become SQL NULL. SQL NULL input propagates, missing or nonobject selections return an empty array, and malformed text raises INVALID_JSON. The target is a constant type descriptor; invalid descriptors raise TYPE_MISMATCH.

**Example**

```sql
json_extract_keys_and_values('{"a":1,"b":"x"}', INT64)
-- → [('a',1),('b',NULL)]
```

## json_extract_keys_and_values_raw

Exports object entries as lexically ordered keys paired with serialized JSON fragments.

```text
json_extract_keys_and_values_raw(document:json|string) -> nullable(array(tuple(string,string)))
json_extract_keys_and_values_raw(document:json|string,path:string) -> nullable(array(tuple(string,string)))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(array(tuple(string,string)))` |
| Aliases | `JSONExtractKeysAndValuesRaw` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; missing/nonobject yields empty SQL array; invalid text errors; typed value conversion failures become NULL |

Produces SQL tuples whose first field is the object key and whose second field is canonical JSON text, preserving quotes and explicit null values. A missing or nonobject selection yields an empty array, whereas SQL NULL input yields SQL NULL. Malformed source text raises INVALID_JSON and excessive serialization output raises RESOURCE_LIMIT.

**Example**

```sql
json_extract_keys_and_values_raw('{"a":1,"b":null}')
-- → [('a','1'),('b','null')]
```

## json_extract_path

Follows literal key components and zero-based subscripts to a native JSON subtree.

```text
json_extract_path(document:json|string,path_part:string,...) -> nullable(json)
json_extract_path(document:json|string,path_parts:array(string)) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(json)` |
| Aliases | `#>`, `jsonb_extract_path` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL or absent/wrong-shaped path yields SQL NULL; selected JSON null remains JSON null |

Traverses up to fifteen string components as literal object keys or zero-based array offsets, with negative offsets measured from the end. SQL NULL, an absent path, or an incompatible intermediate shape returns SQL NULL; selected JSON null remains JSON null. Malformed source text raises INVALID_JSON, and the SQL-array adapter preserves component boundaries.

**Example**

```sql
json_extract_path('{"readings":[3,4]}', 'readings', '0')
-- → 3
```

## json_extract_path_text

Renders a component-addressed document value as unquoted or canonical textual content.

```text
json_extract_path_text(document:json|string,path_part:string,...) -> nullable(string)
json_extract_path_text(document:json|string,path_parts:array(string)) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(string)` |
| Aliases | `#>>`, `jsonb_extract_path_text` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, JSON null or absent/wrong-shaped path yields SQL NULL |

Uses literal object components and zero-based array subscripts, including negative offsets, then unquotes strings or serializes other selected values. JSON null, SQL NULL, absent components, and wrong-shaped traversal all produce SQL NULL. Invalid source text raises INVALID_JSON and more than fifteen variadic components raises INVALID_ARGUMENT.

**Example**

```sql
json_extract_path_text('{"sensor":{"unit":"C"}}', 'sensor', 'unit')
-- → 'C'
```

## json_extract_raw

Returns one canonical JSON fragment, retaining string quotes and the null literal.

```text
json_extract_raw(document:json|string) -> nullable(string)
json_extract_raw(document:json|string,path_part:string|int64,...) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(string)` |
| Aliases | `JSONExtractRaw` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Serializes the component-selected subtree as a single string, even when that subtree is an array or object. Missing selection returns an empty string, SQL NULL propagates, and explicit JSON null produces the text null. Array components use one-based and negative-from-end indexing; malformed source text raises INVALID_JSON.

**Example**

```sql
json_extract_raw('{"unit":"C"}', 'unit')
-- → '"C"'
```

## json_extract_string

Unquotes an actual JSON string reached through literal key or positional components.

```text
json_extract_string(document:json|string) -> nullable(string)
json_extract_string(document:json|string,path_part:string|int64,...) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(string)` |
| Aliases | `JSONExtractString` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing path, JSON null, invalid text, type mismatch or overflow yields SQL NULL |

Accepts only a selected JSON string and never formats numbers or containers as replacement text. JSON null, missing components, malformed source, incompatible value kinds, and SQL NULL all return SQL NULL. One-based indices and negative-from-end offsets address arrays, while resource failures propagate as RESOURCE_LIMIT.

**Example**

```sql
json_extract_string('{"unit":"C"}', 'unit')
-- → 'C'
```

## json_extract_uint

Reads a nonnegative 64-bit count from a selected JSON numeric component.

```text
json_extract_uint(document:json|string) -> nullable(uint64)
json_extract_uint(document:json|string,path_part:string|int64,...) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(uint64)` |
| Aliases | `JSONExtractUInt` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing path, JSON null, invalid text, type mismatch or overflow yields SQL NULL |

Checks the selected number against the UInt64 domain, returning SQL NULL for negatives, overflow, or lossy conversion. Absent components, JSON null, malformed input, and SQL NULL input also yield SQL NULL. Object components remain literal keys and array indices are one-based with negative-from-end support; RESOURCE_LIMIT is preserved.

**Example**

```sql
json_extract_uint('{"count":4}', 'count')
-- → 4
```

## json_from_value

Converts typed SQL data into native JSON without reparsing strings as documents.

```text
json_from_value(value:T) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `nullable(json)` |
| Aliases | `array_to_json`, `as_json`, `row_to_json`, `to_json`, `to_jsonb` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Quotes SQL strings, maps named tuples to objects, and maps unnamed tuples or SQL arrays to native JSON arrays. SQL NULL propagates instead of constructing a JSON-null value; builders provide embedding behavior when needed. Nonfinite floating-point values raise INVALID_ARGUMENT and depth or byte-cap violations raise RESOURCE_LIMIT.

**Example**

```sql
json_from_value('{"a":1}')
-- → "{\"a\":1}"
```

## json_get

Selects one object member or zero-based array slot while retaining its JSON type.

```text
json_get(document:json|string,key:string) -> nullable(json)
json_get(document:json|string,index:int64) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(json)` |
| Aliases | `->`, `json_array_element`, `json_object_field`, `jsonb_array_element`, `jsonb_object_field` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL or absent/wrong-shaped selection yields SQL NULL; selected JSON null remains JSON null |

A string key performs a literal object lookup and an integer selects an array element, with negative indices counting from the end. Absent or wrong-shaped selection and SQL NULL return SQL NULL, while a selected JSON null remains a native null value. Malformed JSON text raises INVALID_JSON; the JSON arrow operator lowers to these overloads.

**Example**

```sql
json_get('[3,4]', -1)
-- → 4
```

## json_get_text

Extracts a direct document field as SQL text, mapping a JSON null to SQL NULL.

```text
json_get_text(document:json|string,key:string) -> nullable(string)
json_get_text(document:json|string,index:int64) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(string)` |
| Aliases | `->>`, `json_array_element_text`, `json_object_field_text`, `jsonb_array_element_text`, `jsonb_object_field_text` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, JSON null or absent/wrong-shaped selection yields SQL NULL |

Unquotes a selected JSON string and serializes selected numbers, booleans, objects, or arrays as canonical text. Literal object keys and zero-based array indices use direct selection, with negative array offsets supported. Missing or wrong-shaped selections and either null form yield SQL NULL; malformed text raises INVALID_JSON.

**Example**

```sql
json_get_text('{"unit":"C"}', 'unit')
-- → 'C'
```

## json_has

Checks component-path presence using one-based indexing without inspecting the stored value.

```text
json_has(document:json|string,path_part:string|int64,...) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(bool)` |
| Aliases | `JSONHas` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Follows up to fifteen literal-key or integer components and returns true for any present value, including JSON null. Missing components return false, SQL NULL propagates, and negative array positions count from the end. Malformed source text raises INVALID_JSON; this operation does not test array-value membership.

**Example**

```sql
json_has('{"readings":[null]}', 'readings', 1)
-- → true
```

## json_hash

Produces a seeded 64-bit SipHash digest of canonical logical document content.

```text
json_hash(document:json) -> nullable(uint64)
json_hash(document:json,seed:uint64) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Hashes versioned canonical JSON bytes with NYX SipHash-64 and an optional seed that defaults to zero, so object key order and equivalent decimal spelling do not alter the digest. SQL NULL input or seed yields SQL NULL. Equal hashes are only candidate equality checks because collisions remain possible; oversized input raises RESOURCE_LIMIT.

**Example**

```sql
json_hash(NULL)
-- → NULL
```

## json_hash_extended

Computes a 128-bit document fingerprint with versioned SipHash and an optional seed.

```text
json_hash_extended(document:json) -> nullable(uint128)
json_hash_extended(document:json,seed:uint64) -> nullable(uint128)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(uint128)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the NYX SipHash-128 result over logical canonical JSON bytes, using zero when no seed is supplied. SQL NULL propagates, and equivalent object ordering or numeric spelling has no effect on the fingerprint. The wider digest does not prove equality or promise PostgreSQL internal-hash compatibility; exceeding a resource limit raises RESOURCE_LIMIT.

**Example**

```sql
json_hash_extended(NULL, 7)
-- → NULL
```

## json_insert

Inserts an absent member or shifts array elements around a specified insertion position.

```text
json_insert(document:json,path:string,value:json) -> nullable(json)
json_insert(document:json,path:string,value:json,insert_after:bool) -> nullable(json)
json_insert(document:json,path_parts:array(string),value:json,insert_after:bool) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary` |
| Returns | `nullable(json)` |
| Aliases | `jsonb_insert` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Adds an absent object key without overwriting an existing one, or inserts before a zero-based array position unless insert_after is true; an index equal to array length appends. Missing parents or nonsingular mutation paths raise INVALID_JSON_PATH. SQL NULL propagates, explicit JSON null is stored, and an oversized new document raises RESOURCE_LIMIT.

**Example**

```sql
json_insert(json_parse('[1,3]'), '$[1]', json_parse('2'))
-- → [1,2,3]
```

## json_key

Returns the object key at a one-based position in canonical lexical ordering.

```text
json_key(document:json|string,position:uint64) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(string)` |
| Aliases | `JSONKey` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Sorts object keys canonically and returns the requested one-based key, independent of source serialization order. Missing, nonobject, or out-of-range selection yields an empty string; SQL NULL input yields SQL NULL. Text input must parse successfully or INVALID_JSON is raised.

**Example**

```sql
json_key('{"b":2,"a":1}', 1)
-- → 'a'
```

## json_keys

Collects a selected object’s field names into a sorted SQL string array.

```text
json_keys(document:json|string) -> nullable(array(string))
json_keys(document:json|string,path:string) -> nullable(array(string))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(array(string))` |
| Aliases | `json_extract_keys`, `JSONExtractKeys` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; missing/nonobject gives empty SQL array; invalid text errors |

Returns keys in canonical lexical order from the root object or an optional singular path selection. A missing path or nonobject returns an empty array, while SQL NULL returns SQL NULL. Malformed source text raises INVALID_JSON and an invalid selector raises INVALID_JSON_PATH; relational expansion is available through json_object_keys.

**Example**

```sql
json_keys('{"b":2,"a":1}')
-- → ['a','b']
```

## json_length

Counts object fields or array members at the root or a chosen document path.

```text
json_length(document:json|string) -> nullable(uint64)
json_length(document:json|string,path:string) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(uint64)` |
| Aliases | `JSONLength` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, missing path or scalar/noncontainer yields SQL NULL |

Returns container cardinality, counting only immediate object fields or array elements and giving zero for an empty container. A scalar, JSON null, missing path, or SQL NULL yields SQL NULL rather than zero. Malformed JSON text raises INVALID_JSON and malformed path syntax raises INVALID_JSON_PATH.

**Example**

```sql
json_length('{"a":1,"b":[2,3]}')
-- → 2
```

## json_match

Evaluates a declarative JSON document predicate with correlated element matching.

```text
json_match(document:json,predicate:json) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(bool)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Supports the documented comparison, membership, existence, type, Boolean, and same-element $elemMatch operators against literal or dotted keys. Missing fields and JSON null remain distinct, and SQL NULL arguments propagate. Unknown operators raise INVALID_ARGUMENT and cross-type ordering without explicit conversion raises TYPE_MISMATCH.

**Example**

```sql
json_match(json_parse('{"reading":8}'), json_parse('{"reading":{"$gt":5}}'))
-- → true
```

## json_merge

Combines object fields left to right, keeping the final value for each overlapping key.

```text
json_merge(document:json,other:json,...) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Shallowly merges up to sixteen native objects, treating each nested object as a whole value rather than recursively merging its children. JSON-null field values remain stored and SQL NULL arguments propagate. Any nonobject argument raises TYPE_MISMATCH, while the combined document must fit RESOURCE_LIMIT.

**Example**

```sql
json_merge(json_parse('{"a":1}'), json_parse('{"a":2,"b":3}'))
-- → {"a":2,"b":3}
```

## json_merge_patch

Applies RFC 7396 patches that recursively update objects and delete null-valued members.

```text
json_merge_patch(document:json,patch:json,...) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies patches from left to right, recursively merging object members, deleting keys whose patch value is JSON null, and replacing the entire target for a nonobject patch. SQL NULL arguments propagate; a native JSON-null patch replaces the root with JSON null. At most sixteen arguments are accepted and oversized patched output raises RESOURCE_LIMIT.

**Example**

```sql
json_merge_patch(json_parse('{"a":1,"b":2}'), json_parse('{"a":null}'))
-- → {"b":2}
```

## json_object_agg

Assembles a keyed JSON document from grouped name/value pairs, retaining SQL null values.

```text
json_object_agg(key:string,value:T) -> nullable(json)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `nullable(json)` |
| Aliases | `json_object_agg_unique`, `JSON_OBJECTAGG`, `jsonb_object_agg`, `jsonb_object_agg_unique` |
| Streaming | Stateful — keeps per-group state. State: unbounded |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL key errors; SQL NULL value embeds JSON null; empty group yields SQL NULL |

Creates a canonical-key-ordered object from group keys and values, embedding SQL NULL values as JSON null and returning SQL NULL for an empty group. SQL NULL keys and duplicate keys raise INVALID_ARGUMENT; no last-key-wins behavior is allowed. Streaming object growth requires explicit retention or a finite budget and fails with RESOURCE_LIMIT on excess.

**Example**

```sql
json_object_agg('reading', 3)
-- → {"reading":3}
```

## json_object_agg_strict

Combines nonnull group entries into an object whose surviving keys must be unique.

```text
json_object_agg_strict(key:string,value:T) -> nullable(json)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `nullable(json)` |
| Aliases | `json_object_agg_unique_strict`, `jsonb_object_agg_strict`, `jsonb_object_agg_unique_strict` |
| Streaming | Stateful — keeps per-group state. State: unbounded |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL key errors; SQL NULL values skipped; empty/all-skipped group yields SQL NULL |

Discards SQL NULL values before checking duplicate keys, but keeps JSON null as a concrete value; if nothing remains, the result is SQL NULL. A SQL NULL key or duplicate surviving key raises INVALID_ARGUMENT, and canonical sorting fixes key order. An admitted streaming state budget bounds the accumulated object and raises RESOURCE_LIMIT rather than evicting entries.

**Example**

```sql
json_object_agg_strict('reading', NULL)
-- → NULL
```

## json_object_keys

Yields one sorted object-member name per relational row.

```text
json_object_keys(document:json) -> table(tuple(key:string))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/unary` |
| Returns | `table(tuple(key:string))` |
| Aliases | `jsonb_object_keys` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; JSON null/nonobject errors |

Emits only object keys in canonical lexical order, without converting field values or building a SQL array. SQL NULL input emits zero rows, while JSON null or any other nonobject raises TYPE_MISMATCH. The table expansion maintains no cross-row state and its per-document result is bounded by RESOURCE_LIMIT.

**Example**

```sql
SELECT *
FROM json_object_keys(json_parse('{"b":2,"a":1}'))
-- → [('a'),('b')]
```

## json_parse

Parses UTF-8 text or bytes into a canonical JSON value with exact decimal numbers.

```text
json_parse(text:string|bytes) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Decodes the complete input as JSON, retaining exact decimal values and distinguishing an explicit JSON null from SQL NULL. SQL NULL propagates, while malformed syntax, duplicate object keys, or invalid UTF-8 raises INVALID_JSON. Depth and byte limits raise RESOURCE_LIMIT when exceeded.

**Example**

```sql
json_parse('{"b":2,"a":1}')
-- → {"a":1,"b":2}
```

## json_parse_as

Decodes an entire JSON value into a constant SQL target type using checked conversion.

```text
json_parse_as(value:json|string,target:type_literal(T)) -> nullable(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `nullable(T)` |
| Aliases | `from_json` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL, invalid text, incompatible target or overflow returns SQL NULL |

Parses text when necessary and converts the complete native value to the supplied ledger descriptor, including explicit array, map, or tuple targets. SQL NULL, syntax failure, incompatible values, and overflow return SQL NULL. Engine and resource failures are not converted to null; RESOURCE_LIMIT still propagates.

**Example**

```sql
json_parse_as('42', INT64)
-- → 42
```

## json_path_each

Streams a finite row sequence of native values selected by JSONPath from each input document.

```text
json_path_each(document:json,path:string) -> table(tuple(value:json))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/binary` |
| Returns | `table(tuple(value:json))` |
| Aliases | `jsonb_path_query` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; matched JSON null emits a JSON-null row |

Emits one row per match in canonical object-key and input-array traversal order, retaining matched JSON null as a native null row. SQL NULL input or no matches emits zero rows; malformed paths raise INVALID_JSON_PATH. This lateral table operation preserves each event’s context without shared state, and excessive fan-out or evaluation work raises RESOURCE_LIMIT.

**Example**

```sql
SELECT *
FROM json_path_each(json_parse('[1,2]'), '$[*]')
-- → [(1),(2)]
```

## json_path_each_tz

Expands timezone-aware path matches into event-local result rows.

```text
json_path_each_tz(document:json,path:string,timezone:string) -> table(tuple(value:json))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/ternary+timezone` |
| Returns | `table(tuple(value:json))` |
| Aliases | `jsonb_path_query_tz` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL emits zero rows; matched JSON null emits one JSON-null row |

Uses the required explicit timezone for temporal JSONPath casts and emits each match in deterministic traversal order, retaining JSON-null matches. SQL NULL input and empty match sequences emit no rows; invalid zones or default-rejected local-time gaps or overlaps raise INVALID_ARGUMENT. Malformed paths raise INVALID_JSON_PATH, and finite lateral expansion obeys per-input RESOURCE_LIMIT without cross-row state.

**Example**

```sql
SELECT *
FROM json_path_each_tz(json_parse('[1,2]'), '$[*]', 'UTC')
-- → [(1),(2)]
```

## json_path_match

Evaluates a JSONPath predicate as true, false, or unknown instead of testing presence.

```text
json_path_match(document:json|string,predicate:string) -> nullable(bool)
json_path_match(document:json|string,predicate:string,variables:json) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `nullable(bool)` |
| Aliases | `@@`, `jsonb_path_match` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL or JSONPath unknown yields SQL NULL; true/false preserved; malformed or nonscalar predicate errors |

Requires a scalar Boolean predicate result and preserves JSONPath three-valued logic, mapping unknown to SQL NULL. SQL NULL input also propagates, and comparisons between incompatible types remain unknown unless explicitly converted. Malformed paths raise INVALID_JSON_PATH, malformed documents raise INVALID_JSON, and nonscalar predicate results raise TYPE_MISMATCH.

**Example**

```sql
json_path_match('{"reading":8}', '$.reading > 5')
-- → true
```

## json_path_match_tz

Resolves temporal JSONPath conditions under a caller-supplied timezone.

```text
json_path_match_tz(document:json|string,path:string,timezone:string) -> nullable(bool)
json_path_match_tz(document:json|string,path:string,variables:json,timezone:string) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary+timezone` |
| Returns | `nullable(bool)` |
| Aliases | `jsonb_path_match_tz` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; base path empty/unknown policy applies; invalid timezone or temporal conversion errors |

Uses the required timezone for date and datetime64 conversions inside a three-valued JSONPath predicate; true and false remain Boolean and unknown becomes SQL NULL. SQL NULL arguments propagate, while an invalid timezone or rejected ambiguous local time raises INVALID_ARGUMENT. Malformed predicate syntax raises INVALID_JSON_PATH, with replay behavior tied to versioned timezone rules.

**Example**

```sql
json_path_match_tz('{"reading":8}', '$.reading > 5', 'UTC')
-- → true
```

## json_path_query

Collects every JSONPath match into one native array in deterministic traversal order.

```text
json_path_query(document:json|string,path:string) -> nullable(json)
json_path_query(document:json|string,path:string,variables:json) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `nullable(json)` |
| Aliases | `json_path_query_array`, `jsonb_path_query_array` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Preserves heterogeneous matches and explicit JSON null, visiting object keys canonically and array elements in source order. No match produces an empty native array, whereas SQL NULL input produces SQL NULL. Malformed JSON or path syntax raises INVALID_JSON or INVALID_JSON_PATH, and excessive path work or output raises RESOURCE_LIMIT.

**Example**

```sql
json_path_query('{"readings":[3,4]}', '$.readings[*]')
-- → [3,4]
```

## json_path_query_first

Selects the earliest item yielded by deterministic JSONPath traversal.

```text
json_path_query_first(document:json|string,path:string) -> nullable(json)
json_path_query_first(document:json|string,path:string,variables:json) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `nullable(json)` |
| Aliases | `jsonb_path_query_first` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL or no match yields SQL NULL; selected JSON null remains JSON null |

Returns the first matching native value using canonical object-key order and original array order, without wrapping it in an array. An empty match sequence or SQL NULL input yields SQL NULL; a matching JSON null remains JSON null. INVALID_JSON_PATH rejects malformed paths and RESOURCE_LIMIT bounds evaluation work.

**Example**

```sql
json_path_query_first('{"readings":[3,4]}', '$.readings[*]')
-- → 3
```

## json_path_query_first_tz

Finds the first path-selected document item using explicit temporal interpretation.

```text
json_path_query_first_tz(document:json|string,path:string,timezone:string) -> nullable(json)
json_path_query_first_tz(document:json|string,path:string,variables:json,timezone:string) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary+timezone` |
| Returns | `nullable(json)` |
| Aliases | `jsonb_path_query_first_tz` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; base path empty/unknown policy applies; invalid timezone or temporal conversion errors |

Selects the first JSONPath match with required timezone-aware date/time casts, preserving a matched JSON null as a native value. No match and SQL NULL input return SQL NULL. Invalid zones, nonexistent local times, or ambiguous instants rejected by the default policy raise INVALID_ARGUMENT; malformed paths raise INVALID_JSON_PATH.

**Example**

```sql
json_path_query_first_tz('[3,4]', '$[*]', 'UTC')
-- → 3
```

## json_path_query_tz

Builds a native match list from JSONPath evaluation with required timezone control.

```text
json_path_query_tz(document:json|string,path:string,timezone:string) -> nullable(json)
json_path_query_tz(document:json|string,path:string,variables:json,timezone:string) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary+timezone` |
| Returns | `nullable(json)` |
| Aliases | `json_path_query_array_tz`, `jsonb_path_query_array_tz` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; base path empty/unknown policy applies; invalid timezone or temporal conversion errors |

Collects all matches into a JSON array while interpreting temporal casts through the explicit timezone and versioned timezone rules. No matches yield an empty array, SQL NULL propagates, and matched JSON null remains an array element. INVALID_ARGUMENT reports invalid zones or rejected local-time gaps or overlaps; INVALID_JSON_PATH and RESOURCE_LIMIT report path or budget failures.

**Example**

```sql
json_path_query_tz('[3,4]', '$[*]', 'UTC')
-- → [3,4]
```

## json_pretty

Formats a native document with sorted keys, LF line breaks, and selectable indentation.

```text
json_pretty(document:json) -> nullable(string)
json_pretty(document:json,indent:uint8) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(string)` |
| Aliases | `jsonb_pretty` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Renders deterministic presentation text with exact numbers and zero to eight spaces per indentation level, defaulting to two. SQL NULL input produces SQL NULL, and formatting never changes the stored document’s semantics. An incompatible indentation raises INVALID_ARGUMENT and excessive rendered size raises RESOURCE_LIMIT.

**Example**

```sql
json_pretty(json_parse('42'))
-- → '42'
```

## json_project

Selects or excludes document fields according to a literal or dotted-path projection.

```text
json_project(document:json,projection:json) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Interprets a projection object as either inclusion or exclusion and retains empty nested objects and explicitly present JSON null values. Missing included fields remain absent, and SQL NULL arguments propagate. Mixing inclusion and exclusion or requesting expression evaluation raises INVALID_ARGUMENT; output is subject to RESOURCE_LIMIT.

**Example**

```sql
json_project(
  json_parse('{"reading":4,"unit":"C"}'),
  json_parse('{"reading":true}')
)
-- → {"reading":4}
```

## json_query

Returns SQL/JSON query results with explicit wrapper, scalar, empty, and error policies.

```text
json_query(document:json|string,path:string) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | default NULL ON EMPTY/ERROR; selected JSON null preserved when scalar result is allowed; explicit SQL/JSON clauses override |

Selects JSON items using WITHOUT ARRAY WRAPPER, WITH CONDITIONAL ARRAY WRAPPER, or WITH ARRAY WRAPPER behavior; unwrapped output requires one allowed item. SQL NULL propagates and default NULL ON EMPTY or ERROR returns SQL NULL, while permitted JSON null remains native null. Explicit ERROR policies expose INVALID_JSON, INVALID_JSON_PATH, or TYPE_MISMATCH for malformed or disallowed results.

**Example**

```sql
json_query('{"readings":[3,4]}', '$.readings')
-- → [3,4]
```

## json_remove

Deletes singular document paths in order and compacts affected array positions.

```text
json_remove(document:json,path:string,...) -> nullable(json)
json_remove(document:json,path_parts:array(string)) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `nullable(json)` |
| Aliases | `json_delete`, `json_delete_path` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Removes up to fifteen selected paths from left to right, leaving absent paths unchanged and producing JSON null when the root is removed. SQL NULL arguments propagate, and deleting an array element shifts subsequent positions before the next path is evaluated. Nonsingular or malformed paths raise INVALID_JSON_PATH; component adapters preserve literal key punctuation.

**Example**

```sql
json_remove(json_parse('{"a":1,"b":2}'), '$.a')
-- → {"b":2}
```

## json_replace

Updates an existing JSON leaf while leaving absent destinations unchanged.

```text
json_replace(document:json,path:string,value:json) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `nullable(json)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Replaces only a present singular selection, allowing root replacement and zero-based array indices without creating missing parents. SQL NULL arguments return SQL NULL, while explicit JSON null is stored as the replacement value. Malformed or nonsingular mutation paths raise INVALID_JSON_PATH and oversized results raise RESOURCE_LIMIT.

**Example**

```sql
json_replace(json_parse('{"a":1}'), '$.a', json_parse('2'))
-- → {"a":2}
```

## json_set

Assigns a JSON leaf and optionally creates missing object ancestors or appends an array slot.

```text
json_set(document:json,path:string,value:json) -> nullable(json)
json_set(document:json,path:string,value:json,create_missing:bool) -> nullable(json)
json_set(document:json,path_parts:array(string),value:json,create_missing:bool) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary` |
| Returns | `nullable(json)` |
| Aliases | `jsonb_set` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Replaces a selected leaf and, by default, creates missing intermediate object keys; array indices are zero-based and an index equal to length appends when creation is enabled. Missing intermediate array slots are never padded and raise INVALID_JSON_PATH. SQL NULL propagates, explicit JSON null is stored, and excessive resulting depth or bytes raises RESOURCE_LIMIT.

**Example**

```sql
json_set(json_parse('{}'), '$.sensor.reading', json_parse('4'))
-- → {"sensor":{"reading":4}}
```

## json_set_lax

Updates a document with a chosen policy for an SQL NULL replacement value.

```text
json_set_lax(document:json,path:string,value:nullable(json),create_missing:bool,null_policy:string) -> nullable(json)
json_set_lax(document:json,path_parts:array(string),value:nullable(json),create_missing:bool,null_policy:string) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/5-ary` |
| Returns | `nullable(json)` |
| Aliases | `jsonb_set_lax` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL document/path/control yields SQL NULL; SQL NULL value follows explicit use_json_null/delete_key/return_target/raise_exception policy |

Uses json_set path and creation rules, with a constant null policy of use_json_null, delete_key, return_target, or raise_exception. SQL NULL document, path, or controls return SQL NULL; explicit JSON null is always a concrete value, while a SQL NULL replacement follows the selected policy. Unknown policies or raise_exception raise INVALID_ARGUMENT, and incompatible intermediate shapes raise TYPE_MISMATCH.

**Example**

```sql
json_set_lax(json_parse('{"a":1}'), '$.a', NULL, TRUE, 'delete_key')
-- → {}
```

## json_strip_nulls

Recursively removes null-valued object members, optionally compacting null array slots.

```text
json_strip_nulls(document:json) -> nullable(json)
json_strip_nulls(document:json,strip_array_nulls:bool) -> nullable(json)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(json)` |
| Aliases | `jsonb_strip_nulls` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Walks nested objects and removes members whose value is JSON null, retaining JSON-null array positions unless strip_array_nulls is true. A root JSON null remains JSON null and SQL NULL input propagates. The operation creates a new native value and raises RESOURCE_LIMIT if traversal or output limits are exceeded.

**Example**

```sql
json_strip_nulls(json_parse('{"a":null,"b":[null,1]}'))
-- → {"b":[null,1]}
```

## json_to_string

Serializes typed SQL data as canonical JSON text with quoted strings and exact decimals.

```text
json_to_string(value:T) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `nullable(string)` |
| Aliases | `json_encode`, `to_json_string`, `toJSONString` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Converts the SQL value structurally before writing canonical JSON text, ordering object keys and preserving exact decimal numbers. SQL strings are quoted rather than reparsed, and SQL NULL produces SQL NULL. Incompatible nonfinite floats raise INVALID_ARGUMENT; excessive output or nesting raises RESOURCE_LIMIT.

**Example**

```sql
json_to_string('sensor')
-- → '"sensor"'
```

## json_type

Identifies a selected native value as object, array, string, number, boolean, or null.

```text
json_type(document:json|string) -> nullable(string)
json_type(document:json|string,path:string) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `nullable(string)` |
| Aliases | `json_typeof`, `jsonb_typeof`, `JSONType` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL or missing path yields SQL NULL; JSON null returns the string null |

Reports the JSON kind at the root or one path-selected subtree using the six documented lowercase labels. SQL NULL and an absent path return SQL NULL, while explicit JSON null returns the string null. Malformed text raises INVALID_JSON, invalid paths raise INVALID_JSON_PATH, and multiple selected items raise TYPE_MISMATCH.

**Example**

```sql
json_type('{"reading":null}', '$.reading')
-- → 'null'
```

## json_unwind

Copies a document once per selected array element, replacing that field and adding a zero-based ordinal.

```text
json_unwind(document:json,path:string) -> table(tuple(document:json,ordinal:nullable(uint64)))
json_unwind(document:json,path:string,preserve_missing:bool) -> table(tuple(document:json,ordinal:nullable(uint64)))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/2..3-ary` |
| Returns | `table(tuple(document:json,ordinal:nullable(uint64)))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL input emits zero rows; absent/null/empty selected array emits zero rows unless preserve_missing=true |

Expands a singularly selected array into copied documents with its field replaced by each element and a zero-based ordinal; a present nonarray scalar emits one unchanged document with SQL NULL ordinal. SQL NULL input emits no rows, while missing, JSON-null, or empty arrays emit no rows unless preserve_missing requests the unchanged document with SQL NULL ordinal. Invalid paths raise INVALID_JSON_PATH and stateless per-event fan-out is bounded by RESOURCE_LIMIT.

**Example**

```sql
SELECT *
FROM json_unwind(json_parse('{"a":[1,2]}'), '$.a')
-- → [({"a":1},0),({"a":2},1)]
```

## json_valid

Checks JSON syntax, UTF-8 encoding, and object-key uniqueness without returning a document.

```text
json_valid(value:json|string|bytes) -> nullable(bool)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `nullable(bool)` |
| Aliases | `is_valid_json`, `isValidJSON` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns true for native JSON and validates text or bytes with the same parser rules, including duplicate-key rejection. SQL NULL propagates, while malformed syntax or invalid UTF-8 returns false. Exceeding a resource limit raises RESOURCE_LIMIT instead of treating a value that could not be checked as valid.

**Example**

```sql
json_valid('{"a":1,"a":2}')
-- → false
```

## json_value

Extracts one scalar through SQL/JSON rules, returning unquoted text or a chosen SQL type.

```text
json_value(document:json|string,path:string) -> nullable(string)
json_value(document:json|string,path:string,target:type_literal(T:scalar)) -> nullable(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `nullable(string), or nullable(T) for an exact scalar target` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | default NULL ON EMPTY and NULL ON ERROR; JSON null maps to SQL NULL; explicit SQL/JSON ERROR or DEFAULT clauses override |

The default result is unquoted scalar text; a constant RETURNING descriptor requests exact scalar conversion and any DEFAULT must match that type. SQL NULL, JSON null, empty selection, and errors produce SQL NULL under the default policies. Objects, arrays, and multiple matches trigger the ON ERROR policy, which may expose TYPE_MISMATCH, INVALID_JSON, or INVALID_JSON_PATH.

**Example**

```sql
json_value('{"reading":4}', '$.reading')
-- → '4'
```

## json_values

Collects scalar path matches into a SQL array while retaining nullable result positions.

```text
json_values(document:json|string,path:string) -> nullable(array(nullable(json)))
json_values(document:json|string,path:string,target:type_literal(T)) -> nullable(array(nullable(T)))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `nullable(array(nullable(json))), or nullable(array(nullable(T)))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | SQL NULL propagates; no matches yields empty SQL array; JSON-null scalar becomes SQL NULL; incompatible typed elements become SQL NULL |

Collects each scalar match and optionally converts it independently to a constant target descriptor, preserving positions when conversion produces SQL NULL. No matches give an empty SQL array, SQL NULL input propagates, and selected JSON null becomes a SQL NULL element. Object or array matches raise TYPE_MISMATCH and malformed paths raise INVALID_JSON_PATH.

**Example**

```sql
json_values('[1,null,2]', '$[*]', INT64)
-- → [1,NULL,2]
```

---

Source: https://nyxdb.io/docs/functions/json/