PolarDB:JSON functions and operators

Operators shared by json and jsonb

Field, element, and path extraction operators return NULL rather than failing when the JSON input does not match the requested structure — for example, when the key or array element does not exist.

Additional operators for jsonb only

The @? and @@ operators suppress the following errors: missing object field or array element, unexpected JSON item type, and datetime and numeric errors. This behavior is useful when querying JSON document collections with varying structure.

to_json and to_jsonb

to_json ( anyelement ) → json
to_jsonb ( anyelement ) → jsonb

Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to JSON arrays and objects. If a cast from the SQL type to json exists, that cast function is used; otherwise, scalar values are represented as JSON strings (with escaping), booleans, numbers, or null as appropriate.

to_json('Fred said "Hi."'::text)
-- → "Fred said \"Hi.\""

to_jsonb(row(42, 'Fred said "Hi."'::text))
-- → {"f1": 42, "f2": "Fred said \"Hi.\""}

array_to_json

array_to_json ( anyarray [, boolean ] ) → json

Converts an SQL array to a JSON array. When the optional boolean parameter is true, line feeds are added between top-level array elements.

array_to_json('{{1,5},{99,100}}'::int[])
-- → [[1,5],[99,100]]

row_to_json

row_to_json ( record [, boolean ] ) → json

Converts an SQL composite value to a JSON object. When the optional boolean parameter is true, line feeds are added between top-level elements.

row_to_json(row(1,'foo'))
-- → {"f1":1,"f2":"foo"}

json_build_array and jsonb_build_array

json_build_array ( VARIADIC "any" ) → json
jsonb_build_array ( VARIADIC "any" ) → jsonb

Builds a heterogeneously-typed JSON array from a variadic argument list. Each argument is converted using to_json or to_jsonb.

json_build_array(1, 2, 'foo', 4, 5)
-- → [1, 2, "foo", 4, 5]

json_build_object and jsonb_build_object

json_build_object ( VARIADIC "any" ) → json
jsonb_build_object ( VARIADIC "any" ) → jsonb

Builds a JSON object from a variadic argument list of alternating keys and values. Key arguments are coerced to text; values are converted using to_json or to_jsonb.

json_build_object('foo', 1, 2, row(3,'bar'))
-- → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

json_object and jsonb_object

json_object ( text[] ) → json
jsonb_object ( text[] ) → jsonb

Builds a JSON object from a text array. The array must either have one dimension with an even number of members (treated as alternating key/value pairs), or two dimensions where each inner array has exactly two elements. All values are converted to JSON strings.

json_object('{a, 1, b, "def", c, 3.5}')
-- → {"a" : "1", "b" : "def", "c" : "3.5"}

json_object('{{a, 1}, {b, "def"}, {c, 3.5}}')
-- → {"a" : "1", "b" : "def", "c" : "3.5"}

A two-argument form takes keys and values from separate text arrays:

json_object ( keys text[], values text[] ) → json
jsonb_object ( keys text[], values text[] ) → jsonb

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

If an extension provides a cast from its type to json (for example, the hstore extension), values converted via these creation functions use that cast rather than being represented as primitive strings.

Expanding and querying JSON

`json_array_elements` and `jsonb_array_elements`

json_array_elements ( json ) → setof json
jsonb_array_elements ( jsonb ) → setof jsonb

Expands the top-level JSON array into a set of JSON values.

select * from json_array_elements('[1,true, [2,false]]');
--    value
-- -----------
--  1
--  true
--  [2,false]

`json_array_elements_text` and `jsonb_array_elements_text`

json_array_elements_text ( json ) → setof text
jsonb_array_elements_text ( jsonb ) → setof text

Expands the top-level JSON array into a set of text values.

select * from json_array_elements_text('["foo", "bar"]');
--    value
-- -----------
--  foo
--  bar

`json_array_length` and `jsonb_array_length`

json_array_length ( json ) → integer
jsonb_array_length ( jsonb ) → integer

Returns the number of elements in the top-level JSON array.

json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
-- → 5

`json_each` and `jsonb_each`

json_each ( json ) → setof record ( key text, value json )
jsonb_each ( jsonb ) → setof record ( key text, value jsonb )

Expands the top-level JSON object into a set of key/value pairs.

select * from json_each('{"a":"foo", "b":"bar"}');
--  key | value
-- -----+-------
--  a   | "foo"
--  b   | "bar"

`json_each_text` and `jsonb_each_text`

json_each_text ( json ) → setof record ( key text, value text )
jsonb_each_text ( jsonb ) → setof record ( key text, value text )

Expands the top-level JSON object into a set of key/value pairs, with values returned as text.

select * from json_each_text('{"a":"foo", "b":"bar"}');
--  key | value
-- -----+-------
--  a   | foo
--  b   | bar

`json_object_keys` and `jsonb_object_keys`

json_object_keys ( json ) → setof text
jsonb_object_keys ( jsonb ) → setof text

Returns the set of keys in the top-level JSON object.

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
--  json_object_keys
-- ------------------
--  f1
--  f2

Extracting sub-objects

`json_extract_path` and `jsonb_extract_path`

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

Extracts a JSON sub-object at the specified path. Equivalent to the #> operator, but writing the path as a variadic list can be more convenient in some cases.

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
-- → "foo"

`json_extract_path_text` and `jsonb_extract_path_text`

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text

Extracts a JSON sub-object at the specified path as text. Equivalent to the #>> operator.

json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
-- → foo

Populating composite types from JSON

`json_populate_record` and `jsonb_populate_record`

json_populate_record ( base anyelement, from_json json ) → anyelement
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

Expands the top-level JSON object into a row matching the composite type of base. Fields whose names match output column names are inserted into those columns; unmatched fields are ignored. When base is NULL, unmatched columns are filled with nulls; otherwise, unmatched columns retain the values from base.

The following type conversion rules apply in order:

• A JSON null converts to SQL NULL in all cases.

• If the output column is of type json or jsonb, the JSON value is reproduced exactly.

• If the output column is a composite (row) type and the JSON value is a JSON object, the fields are converted recursively.

• If the output column is an array type and the JSON value is a JSON array, the elements are converted recursively.

• If the JSON value is a string, its contents are passed to the input conversion function for the column's data type.

• Otherwise, the text representation of the JSON value is passed to the input conversion function.

Use json_populate_record in the FROM clause to make all extracted columns available without duplicate function calls.

create type subrowtype as (d int, e text);
create type myrowtype as (a int, b text[], c subrowtype);

select * from json_populate_record(null::myrowtype,
  '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}');
--  a |   b       |      c
-- ---+-----------+-------------
--  1 | {2,"a b"} | (4,"a b c")

`json_populate_recordset` and `jsonb_populate_recordset`

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

Expands the top-level JSON array of objects into a set of rows matching the composite type of base. Each element is processed as described for json_populate_record.

create type twoints as (a int, b int);

select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]');
--  a | b
-- ---+---
--  1 | 2
--  3 | 4

`json_to_record` and `jsonb_to_record`

json_to_record ( json ) → record
jsonb_to_record ( jsonb ) → record

Expands the top-level JSON object into a row with the composite type defined by an AS clause. The calling query must explicitly define the record structure with an AS clause. Unmatched columns are always filled with nulls.

create type myrowtype as (a int, b text);

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}')
  as x(a int, b text, c int[], d text, r myrowtype);
--  a |    b    |    c    | d |       r
-- ---+---------+---------+---+---------------
--  1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

`json_to_recordset` and `jsonb_to_recordset`

json_to_recordset ( json ) → setof record
jsonb_to_recordset ( jsonb ) → setof record

Expands the top-level JSON array of objects into a set of rows with the composite type defined by an AS clause. Each element is processed as described for json_to_record.

select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]')
  as x(a int, b text);
--  a |  b
-- ---+-----
--  1 | foo
--  2 |

Modifying JSON values

`jsonb_set`

jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb

Returns target with the item at path replaced by new_value. When create_if_missing is true (the default) and the item at path does not exist, new_value is inserted instead. All earlier steps in the path must exist, or target is returned unchanged.

Negative integers in path count from the end of JSON arrays. If the last path step is an out-of-range array index and create_if_missing is true, the new value is added at the beginning of the array (for a negative index) or at the end (for a positive index).

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false)
-- → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]

jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]')
-- → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

`jsonb_set_lax`

jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

When new_value is not NULL, behaves identically to jsonb_set. When new_value is NULL, the behavior depends on null_value_treatment:

jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)
-- → [{"f1":null,"f2":null},2,null,3]

jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')
-- → [{"f1": 99, "f2": null}, 2]

`jsonb_insert`

jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb

Returns target with new_value inserted at path. For array elements, new_value is inserted before the target element when insert_after is false (the default), or after it when true. For object fields, new_value is inserted only if the key does not already exist. All earlier steps in the path must exist, or target is returned unchanged.

Negative integers in path count from the end of JSON arrays. An out-of-range array index adds the value at the beginning (negative index) or the end (positive index) of the array.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
-- → {"a": [0, "new_value", 1, 2]}

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
-- → {"a": [0, 1, "new_value", 2]}

Cleaning and formatting

`json_strip_nulls` and `jsonb_strip_nulls`

json_strip_nulls ( json ) → json
jsonb_strip_nulls ( jsonb ) → jsonb

Removes all object fields with null values from the given JSON value, recursively. Null values that are not object fields (for example, null array elements) are left unchanged.

json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')
-- → [{"f1":1},2,null,3]

`jsonb_pretty`

jsonb_pretty ( jsonb ) → text

Converts the given JSON value to pretty-printed, indented text.

jsonb_pretty('[{"f1":1,"f2":null}, 2]')
-- → [
--       {
--           "f1": 1,
--           "f2": null
--       },
--       2
--   ]

`json_typeof` and `jsonb_typeof`

json_typeof ( json ) → text
jsonb_typeof ( jsonb ) → text

Returns the type of the top-level JSON value as a text string. Possible types: object, array, string, number, boolean, and null.

The null result reflects the JSON value null — not a SQL NULL. A SQL NULL input returns a SQL NULL result, not the string 'null'.

json_typeof('-123.4')           -- → number
json_typeof('null'::json)       -- → null
json_typeof(NULL::json) IS NULL -- → t

Aggregate functions

json_agg aggregates record values into a JSON array. json_object_agg aggregates pairs of values into a JSON object. Their jsonb counterparts — jsonb_agg and jsonb_object_agg — produce jsonb output.

json_agg ( anyelement ) → json
jsonb_agg ( anyelement ) → jsonb

json_object_agg ( "any", "any" ) → json
jsonb_object_agg ( "any", "any" ) → jsonb

JSON path functions

The following functions evaluate JSON path expressions against jsonb values.

All functions in this group accept the same optional parameters:

• vars — a JSON object whose fields provide named values to substitute into the jsonpath expression

• silent — when true, suppresses the same errors as the @? and @@ operators

`jsonb_path_exists`

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

Checks whether the JSON path returns any item for the specified JSON value.

jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- → t

`jsonb_path_match`

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

Returns the result of a JSON path predicate check. Only the first item of the result is evaluated. Returns NULL if the result is not Boolean.

jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}')
-- → t

`jsonb_path_query`

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

Returns all JSON items returned by the JSON path for the specified JSON value.

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}');
--  jsonb_path_query
-- ------------------
--  2
--  3
--  4

`jsonb_path_query_array`

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Returns all JSON items returned by the JSON path as a JSON array.

jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- → [2, 3, 4]

`jsonb_path_query_first`

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Returns the first JSON item returned by the JSON path. Returns NULL if there are no results.

jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- → 2

Timezone-aware variants

The functions jsonb_path_exists_tz, jsonb_path_match_tz, jsonb_path_query_tz, jsonb_path_query_array_tz, and jsonb_path_query_first_tz work identically to their counterparts above, but support comparisons of date/time values that require timezone-aware conversions.

Because these functions depend on the current TimeZone setting, they are marked as stable and cannot be used in indexes. The non-_tz variants are immutable and can be used in indexes, but throw errors if asked to make timezone-dependent comparisons.

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

Navigating JSON with path expressions

Use the $ variable to refer to the JSON value being queried (the context item). Follow $ with one or more accessor operators to traverse the JSON structure.

For example, given this GPS tracker data:

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

Use the .key accessor to descend through JSON objects:

$.track.segments

Use [*] to retrieve all elements of an array:

$.track.segments[*].location

Use a numeric subscript to access a specific element (JSON array indexes are zero-based):

$.track.segments[0].location

Append a method call (preceded by a dot) to process the result of a path step:

$.track.segments.size()

Filter expressions

Filter expressions narrow the results of a path step to only those items that satisfy a condition. Write a filter immediately after the path step it applies to:

? (condition)

SQL/JSON uses three-valued logic: conditions evaluate to true, false, or unknown. Only items for which the filter returns true are included in subsequent path steps. Use the is unknown predicate to test for the unknown value.

Within a filter, @ refers to the value being filtered (the result of the preceding path step).

Examples using the GPS tracker data:

Retrieve all heart rate values above 130:

$.track.segments[*].HR ? (@ > 130)

Get start times for segments with heart rate above 130 (the filter applies to the segment, not the HR value):

$.track.segments[*] ? (@.HR > 130)."start time"

Chain multiple filters to select segments with relevant location coordinates and high heart rate:

$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

Filter at different nesting levels — filter segments by location first, then return high heart rate values:

$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)

Nest filter expressions to return the track size only if any segment has high heart rate values:

$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()

Strict and lax modes

When a path expression does not match the actual JSON structure, a structural error occurs. SQL/JSON path expressions handle structural errors in two modes:

• Lax (default): The path engine automatically adapts the queried data to the specified path. Remaining structural errors are suppressed and converted to empty SQL/JSON sequences. Comparison operators automatically unwrap their operands, so you can compare SQL/JSON arrays directly. An array of size 1 is considered equal to its sole element. Automatic unwrapping is not applied when the path contains type() or size() methods, or when the JSON data contains nested arrays (only the outermost array is unwrapped).

• Strict: An error is raised when a structural error occurs.

Using the GPS tracker data in lax mode, you can omit the explicit array unwrapping:

lax $.track.segments.location

In strict mode, you must explicitly unwrap the array:

strict $.track.segments[*].location

Deviations from the SQL/JSON standard

PolarDB's implementation differs from the SQL/JSON standard in two ways:

• A path expression can be a Boolean predicate, even though the standard allows predicates only in filters. This is required for the @@ operator. For example, the following jsonpath expression is valid:

  $.track.segments[*].HR < 70

• There are minor differences in how regular expression patterns are interpreted in like_regex filters.