JSON functions

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.

json_all_paths

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

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

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

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.

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

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

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.

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

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

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

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

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

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

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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

json_array_map

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

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

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

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.

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

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

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.

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

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

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

json_build_object

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

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

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

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

json_concat

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

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

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

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

json_contains

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

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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

json_exists_all

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

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)

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

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

json_exists_any

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

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)

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

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

json_exists_tz

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

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)

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

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

json_extract

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

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

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

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

json_extract_array

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

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

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

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

json_extract_array_raw

Serializes each selected list element separately as canonical JSON 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))

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

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.

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

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

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

json_extract_float

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

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

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

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

json_extract_int

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

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

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

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.

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))))

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

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.

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)))

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

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.

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

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

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.

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)

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

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.

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

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

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

json_extract_string

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

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

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

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

json_extract_uint

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

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

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

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

json_from_value

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

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

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

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

json_get

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

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

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

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

json_get_text

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

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

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

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

json_has

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

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

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

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

json_hash

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

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

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

json_hash(NULL)
-- → NULL

json_hash_extended

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

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

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

json_hash_extended(NULL, 7)
-- → NULL

json_insert

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

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)

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

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.

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

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

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

json_keys

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

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

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

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

json_length

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

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

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

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

json_match

Evaluates a declarative JSON document predicate with correlated element matching.

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

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

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.

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

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

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.

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

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

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.

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

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

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

json_object_agg_strict

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

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

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

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

json_object_keys

Yields one sorted object-member name per relational row.

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

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

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.

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

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

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.

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

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

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

json_path_each

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

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

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

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.

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

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

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.

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

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

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

json_path_match_tz

Resolves temporal JSONPath conditions under a caller-supplied timezone.

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)

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

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.

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

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

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

json_path_query_first

Selects the earliest item yielded by deterministic JSONPath traversal.

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

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

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.

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)

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

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.

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)

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

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

json_pretty

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

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

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

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

json_project

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

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

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

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.

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

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

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

json_remove

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

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

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

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

json_replace

Updates an existing JSON leaf while leaving absent destinations unchanged.

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

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

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.

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)

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

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.

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)

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

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.

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

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

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.

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

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

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

json_type

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

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

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

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.

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)))

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

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.

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

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

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

json_value

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

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

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

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

json_values

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

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)))

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

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