# JSON functions

JSON functions construct documents, extract paths, transform objects and arrays, and expand documents into table rows. Aggregate variants build one document from several input rows.

SQL NULL and JSON null are different values. The `_text` extraction forms return decoded text and can turn JSON null into SQL NULL; structured forms retain JSON values. JSONB functions normalize objects according to their documented contracts. Path syntax and zero-based JSON array indexes are separate from native Array indexing.

## Example: Extracting a nested document field

```sql
SELECT json_extract_path_text('{"user":{"name":"Ada"}}', 'user', 'name'),
       json_typeof(CAST('[1,2,3]' AS JSON))
FORMAT csv;
```

```text
"Ada","array"
```

## array_to_json

Convert a native array into structural JSON.

### Syntax

```text
array_to_json(array[,pretty])
```

### Arguments

Recursively converts native members and retains exact numbers. Pretty defaults to false; true adds line breaks between top-level members. Native nested empty array shapes are retained.

### Returned value

Native JSON array, or SQL NULL.

### Example

```sql
SELECT array_to_json(array(1,NULL,2)) FORMAT csv;
```

```text
"[1,null,2]"
```

## json_array_elements

Expand top-level JSON array elements into native JSON FROM rows.

### Syntax

```text
FROM json_array_elements(json)
```

### Arguments

Requires an array; nested values and JSON null retain structural identity; SQL NULL gives no rows.

### Returned value

One value column containing native JSON.

### Example

```sql
SELECT json_typeof(value) FROM json_array_elements('[1,true]') FORMAT csv;
```

```text
"number"
"boolean"
```

## json_array_elements_text

Expand a JSON array into decoded text rows.

### Syntax

```text
FROM json_array_elements_text(json)
```

### Arguments

Requires an array; strings decode, other values render compactly, JSON null becomes SQL NULL. Plain nested object order/duplicates remain. SQL NULL produces no rows.

### Returned value

One nullable TEXT value column.

### Example

```sql
SELECT value FROM json_array_elements_text('["x",null]') FORMAT csv;
```

```text
"x"
\N
```

## json_build_array

Construct a native JSON array from SQL values.

### Syntax

```text
json_build_array([value,...])
```

### Arguments

Numbers/booleans retain their JSON types; SQL NULL becomes JSON null. Native collections recurse; BLOB becomes a hex byte string.

### Returned value

Native JSON; zero arguments return an empty array.

### Example

```sql
SELECT json_build_array(1,'two',CAST(1 AS Bool),NULL) FORMAT csv;
```

```text
"[1,""two"",true,null]"
```

## json_build_object

Build native JSON from scalar keys and SQL values, retaining duplicate keys and nested JSON structures.

### Syntax

```text
json_build_object([key, value, ...])
```

### Arguments

An even number of alternating scalar keys and values; NULL or composite keys error; value conversion follows native JSON rules.

### Returned value

Native JSON object; no arguments returns `{}`; NULL values become JSON null.

### Example

```sql
SELECT json_build_object('name','Ada','active',CAST(true AS Bool)) FORMAT csv;
```

```text
"{""name"":""Ada"",""active"":true}"
```

## json_each_text

Expand a JSON object into separate key and text value columns.

### Syntax

```text
FROM json_each_text(json)
```

### Arguments

Requires an object; decoded strings stay text, containers render compactly, JSON null becomes SQL NULL; duplicates and order remain.

### Returned value

TEXT key and nullable TEXT value columns.

### Example

```sql
SELECT key,value FROM json_each_text('{"a":"foo","b":null}') FORMAT csv;
```

```text
"a","foo"
"b",\N
```

## json_extract_path

Extract a native JSON value through literal path components.

### Syntax

```text
json_extract_path(json[,component,...])
```

### Arguments

Literal object keys or signed zero-based indexes; final duplicate key wins. Zero path components select the root.

### Returned value

Native JSON, including JSON null; missing/NULL path components and SQL NULL source give SQL NULL.

### Example

```sql
SELECT json_extract_path('{"a":{"b":1}}','a','b') FORMAT csv;
```

```text
"1"
```

## json_extract_path_text

Extract a JSON value using literal keys and signed array indexes.

### Syntax

```text
json_extract_path_text(json[,path_element,...])
```

### Arguments

Indexes are zero-based; negatives count from the end; duplicate keys select the last; NULL/missing paths give SQL NULL.

### Returned value

Decoded string or rendered value as TEXT; JSON null gives SQL NULL.

### Example

```sql
SELECT json_extract_path_text('{"a":{"b":"x"}}','a','b') FORMAT csv;
```

```text
"x"
```

## json_object_keys

Expand top-level JSON object keys into FROM rows.

### Syntax

```text
FROM json_object_keys(json)
```

### Arguments

Requires an object; input order and duplicate keys are retained; SQL NULL gives no rows.

### Returned value

One json_object_keys TEXT column.

### Example

```sql
SELECT * FROM json_object_keys('{"a":1,"b":2}') FORMAT csv;
```

```text
"a"
"b"
```

## json_populate_record

Fill a record from plain JSON using a native base.

### Syntax

```text
json_populate_record(base,json_object), FROM json_populate_record(base,json_object)
```

### Arguments

Base is a named native Tuple, including a typed NULL. FROM infers fields from a CAST or preceding typed column; an untyped NULL requires AS alias(column native_type,...). Missing fields retain base defaults recursively; JSON null replaces them with SQL NULL. Native field coercion applies. Plain nested JSON fields retain object order/duplicates. Scalar output enforces native Tuple NULL constraints; use Nullable members for missing/null fields.

### Returned value

Named native Tuple as a scalar, or typed FROM columns; SQL NULL JSON retains the base.

### Example

```sql
SELECT * FROM json_populate_record(CAST(tuple(7,'x') AS Tuple(a Int64,b String)),'{"a":1}') AS r FORMAT csv;
```

```text
1,"x"
```

## json_populate_recordset

Fill record rows from plain JSON using a native base.

### Syntax

```text
FROM json_populate_recordset(base,json_array)
```

### Arguments

Base is a named native Tuple, including a typed NULL. FROM infers fields from a CAST or preceding typed column; an untyped NULL requires AS alias(column native_type,...). Missing fields retain base defaults recursively; JSON null replaces them with SQL NULL. Native field coercion applies. Plain nested JSON fields retain object order/duplicates. Recordsets convert only the current row; LIMIT can stop before later-row conversion errors.

### Returned value

Typed FROM rows; SQL NULL JSON produces no rows.

### Example

```sql
SELECT * FROM json_populate_recordset(NULL,'[{"a":1},{"a":2}]') AS r(a INTEGER) FORMAT csv;
```

```text
1
2
```

## json_strip_nulls

Recursively remove null-valued object fields, optionally null array members.

### Syntax

```text
json_strip_nulls(json[,strip_in_arrays])
```

### Arguments

Bool option defaults false; root JSON null remains; existing JSON input contract; SQL NULL propagates.

### Returned value

Native JSON with remaining input member order.

### Example

```sql
SELECT json_strip_nulls('{"a":1,"b":null}') FORMAT csv;
```

```text
"{""a"":1}"
```

## json_to_record

Expand JSON objects into one typed row.

### Syntax

```text
json_to_record(document) AS alias(column native_type[, ...])
```

### Arguments

FROM only; one to 2000 explicitly typed columns. Field keys are case-sensitive; final duplicate keys win. Missing and JSON null fields give SQL NULL. Types follow native declarations, with strict INTEGER/REAL/TEXT conversions. JSON declarations retain strings; other types decode string contents before native coercion. Variant/Dynamic receive decoded scalars before native coercion.

### Returned value

Typed rows. SQL NULL produces one all-NULL row.

### Example

```sql
SELECT * FROM json_to_record('{"a":1,"b":"x"}') AS r(a INTEGER,b TEXT) FORMAT csv;
```

```text
1,"x"
```

## json_to_recordset

Expand JSON object arrays into typed rows.

### Syntax

```text
json_to_recordset(document) AS alias(column native_type[, ...])
```

### Arguments

FROM only; one to 2000 explicitly typed columns. Field keys are case-sensitive; final duplicate keys win. Missing and JSON null fields give SQL NULL. Types follow native declarations, with strict INTEGER/REAL/TEXT conversions. JSON declarations retain strings; other types decode string contents before native coercion. Variant/Dynamic receive decoded scalars before native coercion.

### Returned value

Typed rows. SQL NULL produces zero rows.

### Example

```sql
SELECT * FROM json_to_recordset('[{"a":1},{"a":2}]') AS r(a INTEGER) FORMAT csv;
```

```text
1
2
```

## json_to_tsvector

Build a search vector from selected JSON components.

### Syntax

```text
json_to_tsvector([configuration,]document,filter)
```

### Arguments

Same filters/configurations as jsonb_to_tsvector; original object order and duplicate keys are retained. NULL propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(json_to_tsvector('simple','{"title":"fat cats"}','"string"') AS TEXT) FORMAT csv;
```

```text
"'cats':2 'fat':1"
```

## json_typeof

Report the PostgreSQL JSON type label of the top-level value.

### Syntax

```text
json_typeof(json)
```

### Arguments

Accepts the existing JSON input contract; JSON null is the text label null, distinct from SQL NULL.

### Returned value

TEXT: object, array, string, number, boolean, or null; SQL NULL propagates.

### Example

```sql
SELECT json_typeof('true') FORMAT csv;
```

```text
"boolean"
```

## jsonb_array_elements

Expand a JSONB array into native JSON rows.

### Syntax

```text
FROM jsonb_array_elements(json)
```

### Arguments

Requires an array; nested object keys canonicalize; JSON null remains structural. SQL NULL produces no rows.

### Returned value

One value native JSON column.

### Example

```sql
SELECT jsonb_typeof(value) FROM jsonb_array_elements('[1,null]') FORMAT csv;
```

```text
"number"
"null"
```

## jsonb_array_elements_text

Expand a JSONB array into decoded text rows.

### Syntax

```text
FROM jsonb_array_elements_text(json)
```

### Arguments

Requires an array; nested object keys canonicalize; strings decode, containers render compactly, JSON null becomes SQL NULL. SQL NULL produces no rows.

### Returned value

One nullable TEXT value column.

### Example

```sql
SELECT value FROM jsonb_array_elements_text('["x",null]') FORMAT csv;
```

```text
"x"
\N
```

## jsonb_array_length

Count top-level JSONB array elements.

### Syntax

```text
jsonb_array_length(json)
```

### Arguments

Requires an array; nested containers each count as one element.

### Returned value

INTEGER length; empty array gives zero; SQL NULL propagates.

### Example

```sql
SELECT jsonb_array_length('[1,[2,3],null]') FORMAT csv;
```

```text
3
```

## jsonb_build_array

Build a canonical native JSON array.

### Syntax

```text
jsonb_build_array([value[, ...]])
```

### Arguments

SQL NULL becomes JSON null. Recursive objects use canonical JSONB-style keys; native JSON and immediate legacy JSON subtypes remain structural.

### Returned value

Native JSON array; empty input returns an empty array.

### Example

```sql
SELECT jsonb_build_array(1,NULL,'x') FORMAT csv;
```

```text
"[1,null,""x""]"
```

## jsonb_build_object

Build a canonical native JSON object.

### Syntax

```text
jsonb_build_object(key,value[, ...])
```

### Arguments

Even key/value pairs; keys are non-NULL scalars. Values support recursive native values and directly consumed legacy JSON subtypes. Duplicate keys retain the final value.

### Returned value

Native JSON object; empty input returns an empty object.

### Example

```sql
SELECT jsonb_build_object('a',1,'a',2) FORMAT csv;
```

```text
"{""a"":2}"
```

## jsonb_each

Expand a JSONB object into key and native JSON value columns.

### Syntax

```text
FROM jsonb_each(json)
```

### Arguments

Requires an object; nested duplicate keys normalize; JSON null stays structural. SQL NULL produces no rows.

### Returned value

TEXT key and native JSON value columns.

### Example

```sql
SELECT key,jsonb_typeof(value) FROM jsonb_each('{"a":1}') FORMAT csv;
```

```text
"a","number"
```

## jsonb_each_text

Expand a JSONB object into key and text value columns.

### Syntax

```text
FROM jsonb_each_text(json)
```

### Arguments

Requires an object; final duplicates win. Strings decode; containers render compactly; JSON null becomes SQL NULL. SQL NULL produces no rows.

### Returned value

TEXT key and nullable TEXT value columns.

### Example

```sql
SELECT key,value FROM jsonb_each_text('{"a":"x","b":null}') FORMAT csv;
```

```text
"a","x"
"b",\N
```

## jsonb_extract_path

Extract canonical native JSON at a literal path.

### Syntax

```text
jsonb_extract_path(json[,path_element,...])
```

### Arguments

Literal object keys and signed zero-based Int32 indexes; leading ASCII whitespace is accepted and trailing/invalid indexes yield SQL NULL; negatives count from the end; final duplicate keys win. NULL/missing paths return SQL NULL.

### Returned value

Native JSON, including structural JSON null; objects canonicalize.

### Example

```sql
SELECT jsonb_extract_path('{"a":[1,2]}','a','-1') FORMAT csv;
```

```text
"2"
```

## jsonb_extract_path_text

Extract a canonical JSONB path value as text.

### Syntax

```text
jsonb_extract_path_text(json[,path_element,...])
```

### Arguments

Same literal path contract as jsonb_extract_path; strings decode and containers render compactly.

### Returned value

TEXT, or SQL NULL for missing paths, NULL path elements, or JSON null.

### Example

```sql
SELECT jsonb_extract_path_text('{"a":{"b":"x"}}','a','b') FORMAT csv;
```

```text
"x"
```

## jsonb_insert

Insert JSON at an existing parent without replacing an object field.

### Syntax

```text
jsonb_insert(json,path,replacement[,after])
```

### Arguments

Native Array path with literal keys/Int32 indexes; negative indexes supported. after defaults false. Missing parents unchanged; NULL path members and existing object keys error.

### Returned value

Native JSON with JSONB-style last-key-wins canonicalization; SQL NULL propagates.

### Example

```sql
SELECT jsonb_insert('{"a":[0,1,2]}',array('a','1'),'"new"') FORMAT csv;
```

```text
"{""a"":[0,""new"",1,2]}"
```

## jsonb_object

Construct a canonical JSONB-style object from native arrays.

### Syntax

```text
jsonb_object(pairs) or jsonb_object(keys,values)
```

### Arguments

Accepts an even alternating scalar array, an array of two-member arrays, or equal-length separate key/value arrays. Scalar members convert to text; keys cannot be NULL, NULL values become JSON null; duplicate keys keep the final value.

### Returned value

Canonical native JSON object; SQL NULL array argument propagates.

### Example

```sql
SELECT jsonb_object(array('a','1','b','2')) FORMAT csv;
```

```text
"{""a"":""1"",""b"":""2""}"
```

## jsonb_object_keys

Expand canonical JSONB object keys.

### Syntax

```text
FROM jsonb_object_keys(json)
```

### Arguments

Requires an object; duplicate keys keep the final value and keys sort by byte length/bytes. SQL NULL produces no rows.

### Returned value

One jsonb_object_keys TEXT column.

### Example

```sql
SELECT * FROM jsonb_object_keys('{"b":1,"a":2}') FORMAT csv;
```

```text
"a"
"b"
```

## jsonb_path_exists

Check whether a SQL/JSON Path expression selects any item.

### Syntax

```text
jsonb_path_exists(json,path[,vars[,silent]])
```

### Arguments

Lax/strict paths, filters, variables, exact numeric comparisons/arithmetic and supported methods; vars must be an object; silent suppresses structural/numeric/datetime errors; missing variables and resource limits remain errors. Paths are bounded to 16 KiB/128 punctuation tokens; numeric coefficients/exponents to 1024.

### Returned value

Native Bool; NULL on a suppressed error or SQL NULL argument.

### Example

```sql
SELECT jsonb_path_exists('{"a":[1,2,3]}','$.a[*] ? (@ > 2)') FORMAT csv;
```

```text
true
```

## jsonb_path_exists_tz

Test timezone-aware JSONPath selection.

### Syntax

```text
jsonb_path_exists_tz(target,path[,vars[,silent]])
```

### Arguments

Same bounded paths, object vars, error suppression and completed-prefix handling as ordinary JSONPath functions. Honor explicit offsets; implicit timezone conversions use native UTC. Query is FROM-only; first validates subsequent candidates. NULL propagates.

### Returned value

Bool or NULL

### Example

```sql
SELECT jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]','$[*] ? (@.datetime() < "2015-08-02".datetime())') FORMAT csv;
```

```text
true
```

## jsonb_path_match

Return the SQL truth of one JSONPath predicate result.

### Syntax

```text
jsonb_path_match(target,path[,vars[,silent]])
```

### Arguments

Uses the SQL/JSON Path, variable and request-limit rules of jsonb_path_exists. JSON null results return SQL NULL; empty, multiple and nonboolean results require silent mode or error. Silent errors retain completed prefixes before cardinality validation; missing variables and resource failures remain errors.

### Returned value

Bool or SQL NULL; empty/nonboolean/multiple results error unless silent.

### Example

```sql
SELECT jsonb_path_match('{"a":3}','$.a > 2') FORMAT csv;
```

```text
true
```

## jsonb_path_query

Return selected SQL/JSON Path items as native JSON FROM rows.

### Syntax

```text
FROM jsonb_path_query(json,path[,vars[,silent]])
```

### Arguments

Same path/vars/silent contract as jsonb_path_exists; silent retains fully selected rows before the first suppressed error; SQL NULL gives no rows.

### Returned value

One jsonb_path_query native JSON column; duplicate object keys normalize as JSONB.

### Example

```sql
SELECT * FROM jsonb_path_query('{"a":[1,2,3]}','$.a[*] ? (@ > 1)') FORMAT csv;
```

```text
"2"
"3"
```

## jsonb_path_query_array

Collect selected JSONPath items into one native JSON array.

### Syntax

```text
jsonb_path_query_array(json,path[,vars[,silent]])
```

### Arguments

Same bounded path/vars/silent contract as jsonb_path_query; silent retains completed items before the first suppressible error.

### Returned value

Native JSON array; empty selection gives \[]; SQL NULL propagates.

### Example

```sql
SELECT jsonb_path_query_array('{"a":[1,2,3]}','$.a[*] ? (@ > 1)') FORMAT csv;
```

```text
"[2,3]"
```

## jsonb_path_query_first

Return the first fully selected JSONPath item.

### Syntax

```text
jsonb_path_query_first(json,path[,vars[,silent]])
```

### Arguments

Same bounded path/vars/silent contract as jsonb_path_query. Validate later candidates but retain only the first completed item; silent preserves it before a suppressible error.

### Returned value

Native JSON; empty selection and SQL NULL arguments give SQL NULL.

### Example

```sql
SELECT jsonb_path_query_first('{"a":[1,2,3]}','$.a[*] ? (@ > 1)') FORMAT csv;
```

```text
"2"
```

## jsonb_path_query_first_tz

Retain the first timezone-aware JSONPath item.

### Syntax

```text
jsonb_path_query_first_tz(target,path[,vars[,silent]])
```

### Arguments

Same bounded paths, object vars, error suppression and completed-prefix handling as ordinary JSONPath functions. Honor explicit offsets; implicit timezone conversions use native UTC. Query is FROM-only; first validates subsequent candidates. NULL propagates.

### Returned value

JSON or NULL

### Example

```sql
SELECT jsonb_path_query_first_tz('["2015-08-01 12:00:00-05"]','$[*] ? (@.datetime() < "2015-08-02".datetime())') FORMAT csv;
```

```text
"""2015-08-01 12:00:00-05"""
```

## jsonb_path_query_tz

Expand timezone-aware JSONPath selections in FROM.

### Syntax

```text
jsonb_path_query_tz(target,path[,vars[,silent]])
```

### Arguments

Same bounded paths, object vars, error suppression and completed-prefix handling as ordinary JSONPath functions. Honor explicit offsets; implicit timezone conversions use native UTC. Query is FROM-only; first validates subsequent candidates. NULL input yields no rows.

### Returned value

JSON rows

### Example

```sql
SELECT * FROM jsonb_path_query_tz('["2015-08-01 12:00:00-05"]','$[*] ? (@.datetime() < "2015-08-02".datetime())') FORMAT csv;
```

```text
"""2015-08-01 12:00:00-05"""
```

## jsonb_populate_record

Fill a record from canonical JSONB-style using a native base.

### Syntax

```text
jsonb_populate_record(base,json_object), FROM jsonb_populate_record(base,json_object)
```

### Arguments

Base is a named native Tuple, including a typed NULL. FROM infers fields from a CAST or preceding typed column; an untyped NULL requires AS alias(column native_type,...). Missing fields retain base defaults recursively; JSON null replaces them with SQL NULL. Native field coercion applies. Objects normalize final duplicate keys. Scalar output enforces native Tuple NULL constraints; use Nullable members for missing/null fields.

### Returned value

Named native Tuple as a scalar, or typed FROM columns; SQL NULL JSON retains the base.

### Example

```sql
SELECT * FROM jsonb_populate_record(CAST(tuple(7,'x') AS Tuple(a Int64,b String)),'{"a":1}') AS r FORMAT csv;
```

```text
1,"x"
```

## jsonb_populate_record_valid

Test JSONB record field conversion without throwing conversion errors.

### Syntax

```text
jsonb_populate_record_valid(base,json)
```

### Arguments

Named native Tuple base or typed NULL supplies the schema. Checks native Tuple NULL constraints. Wrong containers and field conversion errors return false; malformed JSON/base schemas and resource errors remain errors.

### Returned value

Native Bool; SQL NULL JSON is valid and gives true.

### Example

```sql
SELECT jsonb_populate_record_valid(CAST(tuple(0) AS Tuple(a UInt8)),'{"a":256}') FORMAT csv;
```

```text
false
```

## jsonb_populate_recordset

Fill record rows from canonical JSONB-style using a native base.

### Syntax

```text
FROM jsonb_populate_recordset(base,json_array)
```

### Arguments

Base is a named native Tuple, including a typed NULL. FROM infers fields from a CAST or preceding typed column; an untyped NULL requires AS alias(column native_type,...). Missing fields retain base defaults recursively; JSON null replaces them with SQL NULL. Native field coercion applies. Objects normalize final duplicate keys. Recordsets convert only the current row; LIMIT can stop before later-row conversion errors.

### Returned value

Typed FROM rows; SQL NULL JSON produces no rows.

### Example

```sql
SELECT * FROM jsonb_populate_recordset(NULL,'[{"a":1},{"a":2}]') AS r(a INTEGER) FORMAT csv;
```

```text
1
2
```

## jsonb_pretty

Render canonical native JSON with four-space indentation.

### Syntax

```text
jsonb_pretty(value)
```

### Arguments

Parses native JSON input and canonicalizes duplicate/order rules before rendering. Numeric token spelling follows native JSON.

### Returned value

Indented TEXT, or SQL NULL.

### Example

```sql
SELECT jsonb_pretty('1') FORMAT csv;
```

```text
"1"
```

## jsonb_set

Return native JSON with a path target replaced or added using JSONB-style object normalization.

### Syntax

```text
jsonb_set(json,path_array,new_json[,create_if_missing])
```

### Arguments

Native Array path uses literal keys and signed zero-based Int32 indexes; missing parents remain unchanged; create defaults true; NULL path members error.

### Returned value

Native JSON; duplicate keys keep the last and object keys sort by byte length/bytes; no separate PostgreSQL binary JSONB format.

### Example

```sql
SELECT jsonb_set('{"a":1}',array('a'),'2') FORMAT csv;
```

```text
"{""a"":2}"
```

## jsonb_set_lax

Update native JSON with explicit SQL NULL treatment.

### Syntax

```text
jsonb_set_lax(target,path,new_value[,create_if_missing[,null_value_treatment]])
```

### Arguments

Native Array path with signed zero-based Int32 indexes; leading ASCII whitespace is accepted. Deletion checks NULL components only when reached. Default create_if_missing is true. SQL NULL new values support use_json_null (default), delete_key, return_target or raise_exception. Non-NULL new values ignore the treatment argument.

### Returned value

Canonical native JSON, or SQL NULL for NULL target/path/create flag.

### Example

```sql
SELECT jsonb_set_lax('{"a":1}',array('a'),NULL,true,'delete_key') FORMAT csv;
```

```text
"{}"
```

## jsonb_strip_nulls

Remove JSON null object fields recursively.

### Syntax

```text
jsonb_strip_nulls(value[,strip_in_arrays])
```

### Arguments

The optional flag defaults to false. True also removes null array members. Root JSON null is retained; object keys follow native JSONB-style canonicalization.

### Returned value

Native JSON, or SQL NULL for NULL input/flag.

### Example

```sql
SELECT jsonb_strip_nulls('{"a":null,"b":1}') FORMAT csv;
```

```text
"{""b"":1}"
```

## jsonb_to_record

Expand JSON objects into one typed row.

### Syntax

```text
jsonb_to_record(document) AS alias(column native_type[, ...])
```

### Arguments

FROM only; one to 2000 explicitly typed columns. Field keys are case-sensitive; final duplicate keys win. Missing and JSON null fields give SQL NULL. Types follow native declarations, with strict INTEGER/REAL/TEXT conversions. JSONB-style keys normalize recursively. JSON declarations retain strings; other types decode string contents before native coercion. Variant/Dynamic receive decoded scalars before native coercion.

### Returned value

Typed rows. SQL NULL produces one all-NULL row.

### Example

```sql
SELECT * FROM jsonb_to_record('{"a":1,"b":"x"}') AS r(a INTEGER,b TEXT) FORMAT csv;
```

```text
1,"x"
```

## jsonb_to_recordset

Expand JSON object arrays into typed rows.

### Syntax

```text
jsonb_to_recordset(document) AS alias(column native_type[, ...])
```

### Arguments

FROM only; one to 2000 explicitly typed columns. Field keys are case-sensitive; final duplicate keys win. Missing and JSON null fields give SQL NULL. Types follow native declarations, with strict INTEGER/REAL/TEXT conversions. JSONB-style keys normalize recursively. JSON declarations retain strings; other types decode string contents before native coercion. Variant/Dynamic receive decoded scalars before native coercion.

### Returned value

Typed rows. SQL NULL produces zero rows.

### Example

```sql
SELECT * FROM jsonb_to_recordset('[{"a":1},{"a":2}]') AS r(a INTEGER) FORMAT csv;
```

```text
1
2
```

## jsonb_to_tsvector

Build a search vector from selected JSONB components.

### Syntax

```text
jsonb_to_tsvector([configuration,]document,filter)
```

### Arguments

Configurations simple/english/russian, default english. Filter is a JSON string or string array: string,numeric,boolean,key,all. Canonical JSONB key order and one gap between selected components. Native search limits apply; NULL propagates.

### Returned value

tsvector

### Example

```sql
SELECT CAST(jsonb_to_tsvector('simple','{"title":"fat cats"}','"string"') AS TEXT) FORMAT csv;
```

```text
"'cats':2 'fat':1"
```

## jsonb_typeof

Return the top-level JSONB type label.

### Syntax

```text
jsonb_typeof(json)
```

### Arguments

Existing JSON input contract; JSON null is the text label null, distinct from SQL NULL.

### Returned value

TEXT: object, array, string, number, boolean, or null; SQL NULL propagates.

### Example

```sql
SELECT jsonb_typeof('null') FORMAT csv;
```

```text
"null"
```

## row_to_json

Convert a native Tuple to a JSON object.

### Syntax

```text
row_to_json(tuple[,pretty])
```

### Arguments

Requires a native Tuple; named fields keep names, positional fields use f1/f2. Nested Tuples become objects; infinities become strings, while native SQL NaN becomes NULL before calls. Pretty Bool defaults false and inserts top-level line breaks.

### Returned value

Native JSON; plain object member order remains; SQL NULL propagates.

### Example

```sql
SELECT row_to_json(tuple(1,'x')) FORMAT csv;
```

```text
"{""f1"":1,""f2"":""x""}"
```

## to_json

Convert a SQL value into native JSON.

### Syntax

```text
to_json(value)
```

### Arguments

Text becomes a JSON string; exact numbers retain digits. Arrays/unnamed tuples become arrays; named tuples/maps become objects. BLOB becomes a hex byte string.

### Returned value

Native JSON; SQL NULL remains SQL NULL.

### Example

```sql
SELECT to_json(array(1,2)) FORMAT csv;
```

```text
"[1,2]"
```

## to_jsonb

Convert native values recursively to canonical JSONB-style native JSON.

### Syntax

```text
to_jsonb(value)
```

### Arguments

Tuples become objects with named or fN fields, including nested Tuples. Arrays and Maps retain structure; BLOB becomes a hex byte string; infinities become Infinity/-Infinity strings; native SQL NaN becomes NULL before calls; directly consumed legacy JSON subtypes stay structural.

### Returned value

Native JSON with final-key-wins canonical objects; SQL NULL propagates.

### Example

```sql
SELECT to_jsonb(tuple(1,'x')) FORMAT csv;
```

```text
"{""f1"":1,""f2"":""x""}"
```

## Core JSON functions and operators

json validates and normalizes a document. json_valid tests validity; json_extract reads a path; json_type reports its kind; json_array and json_object construct documents. json_set, json_insert, json_replace and json_remove edit paths; json_patch applies a merge patch. json_array_length counts array elements. json_quote encodes an SQL value. json_group_array and json_group_object aggregate values. json_each expands immediate children; json_tree also visits descendants and exposes key, value, type, atom, id, parent, fullkey and path columns. The -> operator returns JSON text; ->> returns an SQL scalar. Paths and documents retain a 1,000-container nesting limit.

```sql
SELECT json_extract('{"a":[10,20]}','$.a[1]'),json_set('{"a":1}','$.a',2) FORMAT csv;
```

```text
20,"{""a"":2}"
```

## json_pretty and json_error_position

json_pretty(document\[,indent]) returns indented JSON text. Indentation defaults to four spaces when omitted or NULL. A NULL document returns NULL; invalid JSON raises an error. The optional indent is converted to text.

json_error_position(document) returns zero for accepted standard JSON or supported JSON5 text, numbers and SQL NULL. Invalid text returns a one-based character position, rather than raising its syntax error.

```sql
SELECT json_pretty('{"a":1}','  ') AS document FORMAT json_each_row;
```

```text
{"document":"{\n  \"a\": 1\n}"}
```

```sql
SELECT json_error_position('{"a":1}'),json_error_position('{'),json_error_position(NULL) FORMAT csv;
```

```text
0,2,0
```

## JSON aggregates

See the [aggregate function reference](https://coordinatordb.com/docs/reference/functions/aggregate-functions/reference-index#json-aggregates) for JSON array and object aggregates, including NULL handling and empty-input behavior.