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 ​

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

"Ada","array"

array_to_json ​

Convert a native array into structural JSON.

Syntax ​

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 ​

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

"[1,null,2]"

json_array_elements ​

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

Syntax ​

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 ​

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

"number"
"boolean"

json_array_elements_text ​

Expand a JSON array into decoded text rows.

Syntax ​

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 ​

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

"x"
\N

json_build_array ​

Construct a native JSON array from SQL values.

Syntax ​

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 ​

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

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

json_build_object ​

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

Syntax ​

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 ​

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

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

json_each_text ​

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

Syntax ​

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 ​

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

"a","foo"
"b",\N

json_extract_path ​

Extract a native JSON value through literal path components.

Syntax ​

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 ​

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

"1"

json_extract_path_text ​

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

Syntax ​

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 ​

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

"x"

json_object_keys ​

Expand top-level JSON object keys into FROM rows.

Syntax ​

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 ​

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

"a"
"b"

json_populate_record ​

Fill a record from plain JSON using a native base.

Syntax ​

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 ​

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

1,"x"

json_populate_recordset ​

Fill record rows from plain JSON using a native base.

Syntax ​

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 ​

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

1
2

json_strip_nulls ​

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

Syntax ​

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 ​

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

"{""a"":1}"

json_to_record ​

Expand JSON objects into one typed row.

Syntax ​

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 ​

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

1,"x"

json_to_recordset ​

Expand JSON object arrays into typed rows.

Syntax ​

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 ​

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

1
2

json_to_tsvector ​

Build a search vector from selected JSON components.

Syntax ​

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 ​

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

"'cats':2 'fat':1"

json_typeof ​

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

Syntax ​

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 ​

SELECT json_typeof('true') FORMAT csv;

"boolean"

jsonb_array_elements ​

Expand a JSONB array into native JSON rows.

Syntax ​

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 ​

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

"number"
"null"

jsonb_array_elements_text ​

Expand a JSONB array into decoded text rows.

Syntax ​

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 ​

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

"x"
\N

jsonb_array_length ​

Count top-level JSONB array elements.

Syntax ​

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 ​

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

3

jsonb_build_array ​

Build a canonical native JSON array.

Syntax ​

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 ​

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

"[1,null,""x""]"

jsonb_build_object ​

Build a canonical native JSON object.

Syntax ​

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 ​

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

"{""a"":2}"

jsonb_each ​

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

Syntax ​

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 ​

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

"a","number"

jsonb_each_text ​

Expand a JSONB object into key and text value columns.

Syntax ​

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 ​

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

"a","x"
"b",\N

jsonb_extract_path ​

Extract canonical native JSON at a literal path.

Syntax ​

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 ​

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

"2"

jsonb_extract_path_text ​

Extract a canonical JSONB path value as text.

Syntax ​

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 ​

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

"x"

jsonb_insert ​

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

Syntax ​

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 ​

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

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

jsonb_object ​

Construct a canonical JSONB-style object from native arrays.

Syntax ​

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 ​

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

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

jsonb_object_keys ​

Expand canonical JSONB object keys.

Syntax ​

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 ​

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

"a"
"b"

jsonb_path_exists ​

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

Syntax ​

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 ​

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

true

jsonb_path_exists_tz ​

Test timezone-aware JSONPath selection.

Syntax ​

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 ​

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

true

jsonb_path_match ​

Return the SQL truth of one JSONPath predicate result.

Syntax ​

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 ​

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

true

jsonb_path_query ​

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

Syntax ​

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 ​

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

"2"
"3"

jsonb_path_query_array ​

Collect selected JSONPath items into one native JSON array.

Syntax ​

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 ​

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

"[2,3]"

jsonb_path_query_first ​

Return the first fully selected JSONPath item.

Syntax ​

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 ​

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

"2"

jsonb_path_query_first_tz ​

Retain the first timezone-aware JSONPath item.

Syntax ​

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 ​

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

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

jsonb_path_query_tz ​

Expand timezone-aware JSONPath selections in FROM.

Syntax ​

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 ​

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

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

jsonb_populate_record ​

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

Syntax ​

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 ​

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

1,"x"

jsonb_populate_record_valid ​

Test JSONB record field conversion without throwing conversion errors.

Syntax ​

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 ​

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

false

jsonb_populate_recordset ​

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

Syntax ​

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 ​

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

1
2

jsonb_pretty ​

Render canonical native JSON with four-space indentation.

Syntax ​

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 ​

SELECT jsonb_pretty('1') FORMAT csv;

"1"

jsonb_set ​

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

Syntax ​

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 ​

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

"{""a"":2}"

jsonb_set_lax ​

Update native JSON with explicit SQL NULL treatment.

Syntax ​

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 ​

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

"{}"

jsonb_strip_nulls ​

Remove JSON null object fields recursively.

Syntax ​

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 ​

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

"{""b"":1}"

jsonb_to_record ​

Expand JSON objects into one typed row.

Syntax ​

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 ​

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

1,"x"

jsonb_to_recordset ​

Expand JSON object arrays into typed rows.

Syntax ​

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 ​

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

1
2

jsonb_to_tsvector ​

Build a search vector from selected JSONB components.

Syntax ​

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 ​

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

"'cats':2 'fat':1"

jsonb_typeof ​

Return the top-level JSONB type label.

Syntax ​

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 ​

SELECT jsonb_typeof('null') FORMAT csv;

"null"

row_to_json ​

Convert a native Tuple to a JSON object.

Syntax ​

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 ​

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

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

to_json ​

Convert a SQL value into native JSON.

Syntax ​

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 ​

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

"[1,2]"

to_jsonb ​

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

Syntax ​

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 ​

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

"{""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.

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

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.

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

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

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

0,2,0

JSON aggregates ​

See the aggregate function reference for JSON array and object aggregates, including NULL handling and empty-input behavior.