JSON/JSONB Functions and Operators
For details about the JSON/JSONB data type, see JSON/JSONB Types.
|
Operator |
Left Operand Type |
Right Operand Type |
Return Type |
Description |
Example |
|---|---|---|---|---|---|
|
-> |
Array-json(b) |
int |
json(b) |
Obtains the array-json element. If the index does not exist, NULL is returned. |
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;
?column?
-------------
{"c":"baz"}
(1 row) |
|
-> |
object-json(b) |
text |
json(b) |
Obtains the value by a key. If no record is found, NULL is returned. |
SELECT '{"a": {"b":"foo"}}'::json->'a';
?column?
-------------
{"b":"foo"}
(1 row) |
|
->> |
Array-json(b) |
int |
text |
Obtains the JSON array element. If the index does not exist, NULL is returned. |
SELECT '[1,2,3]'::json->>2; ?column? ---------- 3 (1 row) |
|
->> |
object-json(b) |
text |
text |
Obtains the value by a key. If no record is found, NULL is returned. |
SELECT '{"a":1,"b":2}'::json->>'b';
?column?
----------
2
(1 row) |
|
#> |
container-json (b) |
text[] |
json(b) |
Obtains the JSON object in the specified path. If the path does not exist, NULL is returned. |
SELECT '{"a": {"b":{"c": "foo"}}}'::json #>'{a,b}';
?column?
--------------
{"c": "foo"}
(1 row) |
|
#>> |
container-json (b) |
text[] |
text |
Obtains the JSON object in the specified path. If the path does not exist, NULL is returned. |
SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json #>>'{a,2}';
?column?
----------
3
(1 row) |
For the #> and #>> operators, if no data can be found in the specified path, no error is reported and a NULL value is returned.
|
Operator |
Right Operand Type |
Description |
Example |
|---|---|---|---|
|
@> |
jsonb |
Specifies whether the top layer of the JSON on the left contains all items of the top layer of the JSON on the right. |
'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
|
<@ |
jsonb |
Specifies whether all items in the JSON file on the left exist at the top layer of the JSON file on the right. |
'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
|
? |
text |
Specifies whether the string of the key or element exists at the top layer of the JSON value. |
'{"a":1, "b":2}'::jsonb ? 'b' |
|
?| |
text[] |
Specifies whether any of these array strings exists as top-layer keys. |
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
|
?& |
text[] |
Specifies whether all these array strings exist as top-layer keys. |
'["a", "b"]'::jsonb ?& array['a', 'b'] |
|
= |
jsonb |
Determines the size between two JSONB files, which is the same as the jsonb_eq function. |
/ |
|
<> |
jsonb |
Determines the size between two JSONB files, which is the same as the jsonb_ne function. |
/ |
|
< |
jsonb |
Determines the size between two JSONB files, which is the same as the jsonb_lt function. |
/ |
|
> |
jsonb |
Determines the size between two JSONB files, which is the same as the jsonb_gt function. |
/ |
|
<= |
jsonb |
Determines the size between two JSONB files, which is the same as the jsonb_le function. |
/ |
|
>= |
jsonb |
Determines the size between two JSONB files, which is the same as the jsonb_ge function. |
/ |
Functions Supported by JSON/JSONB
- array_to_json(anyarray [, pretty_bool])
Description: Returns an array as JSON. It combines a multi-dimensional array into a JSON array. Line feeds will be added between one-dimensional elements if pretty_bool is true.
Return type: json
For example:openGauss=# SELECT array_to_json('{{1,5},{99,100}}'::int[]); array_to_json ------------------ [[1,5],[99,100]] (1 row) - row_to_json(record [, pretty_bool])
Description: Returns a row as JSON. Line feeds will be added between level-1 elements if pretty_bool is true.
Return type: json
For example:
openGauss=# SELECT row_to_json(row(1,'foo')); row_to_json --------------------- {"f1":1,"f2":"foo"} (1 row)
- json_array_element(array-json, integer), jsonb_array_element(array-jsonb, integer)
Description: Same as the operator `->`, which returns the element with the specified index in the array.
Return type: json, jsonb
For example:openGauss=# SELECT json_array_element('[1,true,[1,[2,3 ]],null]',2); json_array_element -------------------- [1,[2,3]] (1 row)
- json_array_element_text(array-json, integer), jsonb_array_element_text(array-jsonb, integer)
Description: Same as the operator `->>`, which returns the element with the specified index in the array.
Return type: text, text
For example:
openGauss=# SELECT json_array_element_text('[1,true,[1,[2,3]],null]',2); json_array_element_text ----------------------- [1,[2,3]] (1 row)
- json_object_field(object-json, text), jsonb_object_field(object-jsonb, text)
Description: Same as the operator `->`, which returns the value of a specified key in an object.
Return type: json, json
For example:openGauss=# SELECT json_object_field('{"a": {"b":"foo"}}','a'); json_object_field ------------------- {"b":"foo"} (1 row) - json_object_field_text(object-json, text), jsonb_object_field_text(object-jsonb, text)
Description: Same as the operator `->`, which returns the value of a specified key in an object.
Return type: text, text
For example:openGauss=# SELECT json_object_field_text('{"a": {"b":"foo"}}','a'); json_object_field_text ---------------------- {"b":"foo"} (1 row)
- json_extract_path(json, VARIADIC text[]), jsonb_extract_path((jsonb, VARIADIC text[])
Description: Equivalent to the operator `#>` searches for JSON based on the path specified by $2 and returns the result.
Return type: json, jsonb
For example:openGauss=# SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6'); json_extract_path ------------------- "stringy" (1 row)
- json_extract_path_op(json, text[]), jsonb_extract_path_op(jsonb, text[])
Description: It is equivalent to the operator `#>`, searches for JSON based on the path specified by $2, and returns the result.
Return type: json, jsonb
For example:openGauss=# SELECT json_extract_path_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']); json_extract_path_op --------------------- "stringy" (1 row)
- json_extract_path_text(json, VARIADIC text[]), jsonb_extract_path_text((jsonb, VARIADIC text[])
Description: It is equivalent to the operator `#>>`, searches for JSON based on the path specified by $2, and returns the result.
Return type: text, text
For example:openGauss=# SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6'); json_extract_path_text ----------------------- stringy (1 row)
- json_extract_path_text_op(json, text[]), jsonb_extract_path_text_op(jsonb, text[])
Description: It is equivalent to the operator `#>>`, searches for JSON based on the path specified by $2, and returns the result.
Return type: text, text
For example:openGauss=# SELECT json_extract_path_text_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']); json_extract_path_text_op -------------------------- stringy (1 row)
- Json_array_elements(array-json), jsonb_array_elements(array-jsonb)
Description: Splits an array. Each element returns a row.
Return type: json, jsonb
For example:openGauss=# SELECT json_array_elements('[1,true,[1,[2,3]],null]'); json_array_elements --------------------- 1 true [1,[2,3]] null (4 rows)
- Json_array_elements_text(array-json), jsonb_array_elements_text(array-jsonb)
Description: Splits an array. Each element returns a row.
Return type: text, text
For example:openGauss=# SELECT * FROM json_array_elements_text('[1,true,[1,[2,3]],null]'); value ----------- 1 true [1,[2,3]] (4 rows)
- json_array_length(array-json), jsonb_array_length(array-jsonb)
Description: Returns the array length.
Return type: integer
For example:openGauss=# SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4,null]'); json_array_length ------------------- 6 (1 row)
- json_each(object-json), jsonb_each(object-jsonb)
Description: Splits each key-value pair of an object into one row and two columns.
Return type: setof(key text, value json), setof(key text, value jsonb)
For example:openGauss=# SELECT * FROM json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); key | value -----+---------- f1 | [1,2,3] f2 | {"f3":1} f4 | null (3 rows)
- json_each_text(object-json), jsonb_each_text(object-jsonb)
Description: Splits each key-value pair of an object into one row and two columns.
Return type: setof(key text, value text), setof(key text, value text)
For example:openGauss=# SELECT * FROM json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); key | value -----+---------- f1 | [1,2,3] f2 | {"f3":1} f4 | (3 rows)
- json_object_keys(object-json), jsonb_object_keys(object-jsonb)
Description: Returns all keys at the top layer of the object.
Return type: SETOF text
For example:openGauss=# SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}'); json_object_keys ------------------ f1 f2 f1 (3 rows)
- JSONB deduplication operations:
openGauss=# SELECT jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}'); jsonb_object_keys ------------------- f1 f2 (2 rows)
- json_populate_record(anyelement, object-json [, bool]), jsonb_populate_record(anyelement, object-jsonb [, bool])
Description: $1 must be a compound parameter. Each key-value in the object-json file is split. The key is used as the column name to match the column name in $1 and fill in the $1 format.
Return type: anyelement, anyelement
For example:openGauss=# CREATE TYPE jpop as (a text, b int, c bool); CREATE TYPE openGauss=# SELECT * FROM json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}'); a | b | c --------+---+--- blurfl | | (1 row)openGauss=# SELECT * FROM json_populate_record((1,1,null)::jpop,'{"a":"blurfl","x":43.2}'); a | b | c --------+---+--- blurfl | 1 | (1 row)
- json_populate_record_set(anyelement, array-json [, bool]), jsonb_populate_record_set(anyelement, array-jsonb [, bool])
Description: Performs the preceding operations on each element in the $2 array by referring to the json_populate_record and jsonb_populate_record functions. Therefore, each element in the $2 array must be of the object-json type.
Return type: setof anyelement, setof anyelement
For example:openGauss=# CREATE TYPE jpop as (a text, b int, c bool); CREATE TYPE openGauss=# SELECT * FROM json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]'); a | b | c ---+---+--- 1 | 2 | 3 | 4 | (2 rows)
- json_typeof(json), jsonb_typeof(jsonb)
Description: Checks the JSON type.
Return type: text, text
For example:openGauss=# SELECT value, json_typeof(value) FROM (values (json '123.4'), (json '"foo"'), (json 'true'), (json 'null'), (json '[1, 2, 3]'), (json '{"x":"foo", "y":123}'), (NULL::json)) AS data(value); value | json_typeof ----------------------+------------- 123.4 | number "foo" | string true | boolean null | null [1, 2, 3] | array {"x":"foo", "y":123} | object | (7 rows)
- json_build_array( [VARIADIC "any"] )
Description: Constructs a JSON array from a variable parameter list.
Return type: array-json
For example:openGauss=# SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}',''); json_build_array --------------------------------------------------------------------------- ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}, ""] (1 row)
- json_build_object( [VARIADIC "any"] )
Description: Constructs a JSON object from a variable parameter list. The number of input parameters must be an even number. Every two input parameters form a key-value pair. Note that the value of a key cannot be null.
Return type: object-json
For example:openGauss=# SELECT json_build_object(1,2); json_build_object ------------------- {"1" : 2} (1 row)
- json_to_record(object-json, bool)
Description: Like all functions that return record, the caller must explicitly define the structure of the record with an AS clause. The key-value pair of object-json is split and reassembled. The key is used as a column name to match and fill in the structure of the specified record.
Return type: record
For example:openGauss=# SELECT * FROM json_to_record('{"a":1,"b":"foo","c":"bar"}',true) AS x(a int, b text, d text); a | b | d ---+-----+--- 1 | foo | (1 row)
- json_to_recordset(array-json, bool)
Description: Executes the preceding function on each element in the array by referring to the json_to_record function. Therefore, each element in the array must be object-json.
Return type: SETOF record
For example:openGauss=# SELECT * FROM json_to_recordset( openGauss(# '[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]', openGauss(# false openGauss(# ) as x(a int, b text, c boolean); a | b | c ---+-----+--- 1 | foo | 2 | bar | t (2 rows)
- json_object(text[]), json_object(text[], text[])
Description: Constructs an object-json from a text array. This is an overloaded function. When the input parameter is a text array, the array length must be an even number, and members are considered as alternate key-value pairs. When two text arrays are used, the first array is considered as a key, and the second array a value. The lengths of the two arrays must be the same. Note that the value of a key cannot be null.
Return type: object-json
For example:openGauss=# SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}'); json_object ------------------------------------------------------- {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"} (1 row)openGauss=# SELECT json_object('{a,b,"a b c"}', '{a,1,1}'); json_object --------------------------------------- {"a" : "a", "b" : "1", "a b c" : "1"} (1 row)
- json_agg(any)
Description: Aggregates values into a JSON array.
Return type: array-json
For example:openGauss=# SELECT * FROM classes; name | score -----+------- A | 2 A | 3 D | 5 D | (4 rows)
openGauss=# SELECT name, json_agg(score) score FROM classes GROUP BY name ORDER BY name; name | score -----+----------------- A | [2, 3] D | [5, null] | [null] (3 rows)
- json_object_agg(any, any)
Description: Aggregates values into a JSON object.
Return type: object-json
For example:openGauss=# SELECT * FROM classes; name | score -----+------- A | 2 A | 3 D | 5 D | (4 rows)
openGauss=# SELECT json_object_agg(name, score) FROM classes GROUP BY name ORDER BY name; json_object_agg ------------------------- { "A" : 2, "A" : 3 } { "D" : 5, "D" : null } (2 rows)
- jsonb_contained(jsonb, jsonb)
Description: Same as the operator `<@`, determines whether all elements in $1 exist at the top layer of $2.
Return type: Boolean
For example:openGauss=# SELECT jsonb_contained('[1,2,3]', '[1,2,3,4]'); jsonb_contained ----------------- t (1 row)
- jsonb_contains(jsonb, jsonb)
Description: Same as the operator `@>`, checks whether all top-layer elements in $1 are contained in $2.
Return type: Boolean
For example:openGauss=# SELECT jsonb_contains('[1,2,3,4]', '[1,2,3]'); jsonb_contains ---------------- t (1 row)
- jsonb_exists(jsonb, text)
Description: Same as the operator `?`, determines whether all elements in the string array $2 exist at the top layer of $1 in the form of key\elem\scalar.
Return type: Boolean
For example:openGauss=# SELECT jsonb_exists('["1",2,3]', '1'); jsonb_exists -------------- t (1 row)
- jsonb_exists_all(jsonb, text[])
Description: Same as the operator `?&`, checks whether all elements in the string array $2 exist at the top layer of $1 in the form of key\elem\scalar.
Return type: Boolean
For example:openGauss=# SELECT jsonb_exists_all('["1","2",3]', '{1, 2}'); jsonb_exists_all ------------------ t (1 row)
- jsonb_exists_any(jsonb, text[])
Description: Same as the operator `?|`, checks whether all elements in the string array $2 exist at the top layer of $1 in the form of key\elem\scalar.
Return type: Boolean
For example:openGauss=# SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}'); jsonb_exists_any ------------------ t (1 row)
- jsonb_cmp(jsonb, jsonb)
Description: Compares values. A positive value indicates greater than, a negative value indicates less than, and 0 indicates equal.
Return type: integer
For example:openGauss=# SELECT jsonb_cmp('["a", "b"]', '{"a":1, "b":2}'); jsonb_cmp ----------- -1 (1 row)
- jsonb_eq(jsonb, jsonb)
Description: Same as the operator `=`, compares two values.
Return type: Boolean
For example:openGauss=# SELECT jsonb_eq('["a", "b"]', '{"a":1, "b":2}'); jsonb_eq ---------- f (1 row)
- jsonb_ne(jsonb, jsonb)
Description: Same as the operator `<>`, compares two values.
Return type: Boolean
For example:openGauss=# SELECT jsonb_ne('["a", "b"]', '{"a":1, "b":2}'); jsonb_ne ---------- t (1 row)
- jsonb_gt(jsonb, jsonb)
Description: Same as the operator `>`, compares two values.
Return type: Boolean
For example:
openGauss=# SELECT jsonb_gt('["a", "b"]', '{"a":1, "b":2}'); jsonb_gt ---------- f (1 row)
- jsonb_ge(jsonb, jsonb)
Description: Same as the operator `>=`, compares two values.
Return type: Boolean
For example:openGauss=# SELECT jsonb_ge('["a", "b"]', '{"a":1, "b":2}'); jsonb_ge ---------- f (1 row)
- jsonb_lt(jsonb, jsonb)
Description: Same as the operator `<`, compares two values.
Return type: Boolean
For example:openGauss=# SELECT jsonb_lt('["a", "b"]', '{"a":1, "b":2}'); jsonb_lt ---------- t (1 row)
- jsonb_le(jsonb, jsonb)
Description: Same as the operator `<=`, compares two values.
Return type: Boolean
For example:openGauss=# SELECT jsonb_le('["a", "b"]', '{"a":1, "b":2}'); jsonb_le ---------- t (1 row)
- to_json(anyelement)
Description: Converts parameters to `json`.
Return type: json
For example:openGauss=# SELECT to_json('{1,5}'::text[]); to_json ----------- ["1","5"] (1 row) - jsonb_hash(jsonb)
Description: Performs the hash operation on JSONB.
Return type: integer
For example:openGauss=# SELECT jsonb_hash('[1,2,3]'); jsonb_hash ------------ -559968547 (1 row) - Other functions
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
