JSON/JSONB Functions and Operators

For details about the JSON/JSONB data type, see JSON/JSONB Types.

Table 1 JSON/JSONB common operators

Operator	Left Operand Type	Right Operand Type	Return Type	Description	Example	Example Result
->	Array-json(b)	int	json(b)	Obtains the array-json element. If the subscript does not exist, NULL is returned.	'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2	{"c":"baz"}
->	object-json(b)	text	json(b)	Obtains the value by a key. If no record is found, NULL is returned.	'{"a": {"b":"foo"}}'::json->'a'	{"b":"foo"}
->>	Array-json(b)	int	text	Obtains the JSON array element. If the subscript does not exist, NULL is returned.	'[1,2,3]'::json->>2	3
->>	object-json(b)	text	text	Obtains the value by a key. If no record is found, NULL is returned.	'{"a":1,"b":2}'::json->>'b'	2
#>	container-json (b)	text[]	json(b)	Obtains the JSON object in the specified path. If the path does not exist, NULL is returned.	'{"a": {"b":{"c": "foo"}}}'::json #>'{a,b}'	{"c": "foo"}
#>>	container-json (b)	text[]	text	Obtains the JSON object in the specified path. If the path does not exist, NULL is returned.	'{"a":[1,2,3],"b":[4,5,6]}'::json #>>'{a,2}'	3

CAUTION: For the #> and #>> operators, if no data can be found in the specified path, no error is reported and a NULL value is returned.

Table 2 Additional JSONB support for operators

Operator	Right Operand Type	Description	Example
@>	jsonb	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	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	Whether the string of the key or element exists at the top layer of the JSON value.	'{"a":1, "b":2}'::jsonb ? 'b'
?\|	text[]	Whether any of these array strings exists as top-layer keys.	'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c']
?&	text[]	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. A multi-dimensional array becomes a JSON array of arrays. If the value of pretty\_bool is true, a newline character is added between one-dimensional elements.
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. If the value of preretty\_bool is true, a newline character is added between one-dimensional elements.
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 subscript 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 subscript 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: Same as 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"}}', 'f4','f6');
   json_extract_path_op
  ---------------------
   "stringy"
  (1 row)
```
json_extract_path_text(json, VARIADIC text[]), jsonb_extract_path_text((jsonb, VARIADIC text[])
Description: Equivalent to the operator `#>`, searches for JSON based on the path specified by $2 and return 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: Same as 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"}}', '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
  postgres=# 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
  postgres=# 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)
  postgres-# 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)
```
```
  postgres=# 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

Description: Internal functions used by GIN indexes and JSON\JSONB aggregate functions.

 gin_compare_jsonb
 gin_consistent_jsonb
 gin_consistent_jsonb_hash
 gin_extract_jsonb
 gin_extract_jsonb_hash
 gin_extract_jsonb_query
 gin_extract_jsonb_query_hash
 gin_triconsistent_jsonb
 gin_triconsistent_jsonb_hash

 json_agg_transfn
 json_agg_finalfn
 json_object_agg_transfn
 json_object_agg_finalfn