JSON/JSONB Functions

array_to_json(anyarray [, pretty_bool])

Description: Returns an array of JSON type. A multidimensional array becomes an array of JSON arrays. If pretty_bool is set to true, line breaks will be added between one-dimensional elements.

Return type: json.

Example:

1
2
3
4
5

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 rows of JSON type. If pretty_bool is set to true, line breaks will be added between first-level elements.

Return type: json.

Example:

1
2
3
4
5

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

json_agg(any)

Description: Aggregates values into a JSON array.

Return type: array-json.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT * FROM classes;
 name | score
 -----+-------
 A    |     2
 A    |     3
 D    |     5
 D    | 
(4 rows)    
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: json.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT * FROM classes;
 name | score
 -----+-------
 A    |     2
 A    |     3
 D    |     5
 D    |
(4 rows)
 
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)

json_build_array(VARIADIC "any")

Description: Constructs a possibly heterogeneously typed JSON array from a variadic parameter list.

Return type: json.

Example:

1
2
3
4
5

SELECT json_build_array(1,2,'3',4,5);
 json_build_array
-------------------
 [1, 2, "3", 4, 5]
(1 row)

json_build_object(VARIADIC "any")

Description: Constructs a JSON object from a variadic parameter list. The parameter list consists of alternating keys and values. The number of input parameters must be even, with every two parameters forming a key-value pair. Note that the key cannot be null.

Return type: json.

Example:

1
2
3
4
5

SELECT json_build_object('foo',1,'bar',2);
   json_build_object
------------------------
 {"foo" : 1, "bar" : 2}
(1 row)

json_object(text[]), json_object(text[], text[])

Description: Constructs a JSON object from a text array.

This is an overloaded function. When the input is a single text array, its length must be even, and the members are treated as alternately occurring key/value pairs. When two text arrays are provided, the first array is treated as keys and the second as values. Both arrays must have the same length. Keys cannot be null.

Return type: json.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

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

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

SELECT json_object('{a,b,"a b c"}', '{a,1,1}');
                json_object
 ---------------------------------------
 {"a" : "a", "b" : "1", "a b c" : "1"}
(1 row)

to_json(anyelement)

Description: Converts parameters to JSON.

Return type: json.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT to_json('Fred said "Hi."'::text);
       to_json
---------------------
 "Fred said \"Hi.\""
(1 row)
—— Convert json_tbl_2 to JSON.
postgres=# SELECT * FROM json_tbl_2;
  a |  b 
 ---+-----
  1 | aaa 
  1 | bbb 
  2 | ccc  
  2 | ddd 
 (4 rows) 
postgres=# SELECT to_json(t.*) FROM json_tbl_2 t;
      to_json
-------------------
 {"a":1,"b":"bbb"}
 {"a":2,"b":"ddd"}
 {"a":1,"b":"aaa"}
 {"a":2,"b":"ccc"}
(4 rows)

json_strip_nulls(json)

Description: All object fields with null values are omitted, while other values remain unchanged.

Return type: json.

Example:

1
2
3
4
5

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

json_object_field(json, text)

Description: Similar to the operator ->, it returns the value corresponding to the specified key in the object.

Return type: json.

Example:

1
2
3
4
5

SELECT json_object_field('{"a": {"b":"foo"}}','a');
 json_object_field
-------------------
 {"b":"foo"}
(1 row)

json_object_field_text(object-json, text)

Description: Similar to the operator ->>, it returns the value corresponding to the specified key in the object.

Return type: text.

Example:

1
2
3
4
5

SELECT json_object_field_text('{"a": {"b":"foo"}}','a');
 json_object_field_text
------------------------
 {"b":"foo"}
(1 row)

json_array_element(array-json, integer)

Description: Similar to the operator ->, it returns the element at the specified index in the array.

Return type: json.

Example:

1
2
3
4
5

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)

Description: Similar to the operator ->>, it returns the element at the specified index in the array.

Return type: text.

Example:

1
2
3
4
5

SELECT json_array_element_text('[1,true,[1,[2,3]],null]',2);
 json_array_element_text
-------------------------
 [1,[2,3]]
(1 row)

json_extract_path(json, VARIADIC text[])

Description: Same as the operator #>, it returns the JSON value of the path pointed to by $2.

Return type: json.

Example:

1
2
3
4
5

SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
 json_extract_path
-------------------
 "stringy"
(1 row)

json_extract_path_text(json, VARIADIC text[])

Description: Same as the operator #>>, it returns the text value of the path pointed to by $2.

Return type: text.

Example:

1
2
3
4
5

SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
 json_extract_path_text
------------------------
 stringy
(1 row)

json_array_elements(array-json)

Description: Splits an array, returning each element as a row.

Return type: json.

Example:

1
2
3
4
5
6
7
8

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)

Description: Splits an array, returning each element as a row.

Return type: text.

Example:

1
2
3
4
5
6
7
8

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)

Description: Returns the array length.

Return type: integer.

Example:

1
2
3
4
5

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

json_object_keys(object-json)

Description: Returns all top-level keys in the object.

Return type: text.

Example:

1
2
3
4
5
6
7

SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
 json_object_keys
------------------
 f1
 f2
 f1
(3 rows)

json_each(object-json)

Description: Splits each key-value pair of the object into one row with two columns.

Return type: setof(key text, value json).

Example:

1
2
3
4
5
6
7

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)

Description: Splits each key-value pair of the object into one row with two columns.

Return type: setof(key text, value text).

Example:

1
2
3
4
5
6
7

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_populate_record(anyelement, object-json [, bool])

Description: $1 must be a parameter of a composite type. It splits each key-value pair in object-json, using the key as the column name to match and find the corresponding column name in $1, then fills it into the format of $1.

When the input parameter in a JSON/JSONB function is of a composite type, you can use CREATE EXTERNAL TABLE to define the composite type, for example:

1	CREATE EXTERNAL TABLE jpop2(a text, b INT, c timestamp);

Return type: anyelement.

Example:

1
2
3
4
5

SELECT * FROM json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}');
   a    | b | c
--------+---+---
 blurfl |   |
(1 row)

json_populate_recordset(anyelement, array-json [, bool])

Description: Perform the operation described above on each element of the $2 array by referring to the functions json_populate_record and jsonb_populate_record. Thus, each element of the $2 array must be of the object-json type.

Return type: setof anyelement.

Example:

1
2
3
4
5
6
7

CREATE TYPE jpop AS (a text, b INT, c timestamp);
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_to_record(object-json)

Description: As with all functions that return a record, the caller must explicitly define the structure of the record with an AS clause. It splits and reassembles the key-value pairs of object-json, using the key as the column name to match and fill the structure of the record specified by the AS clause.

Return type: record.

Example:

1
2
3
4
5

SELECT * FROM json_to_record('{"a":1,"b":"foo","c":"bar"}'::json) as x(a int, b text, d text);
 a |  b  | d
---+-----+---
 1 | foo |
(1 row)

json_to_recordset(array-json)

Description: Executes the preceding function on each element within the array by referring to the function json_to_record. Hence, every element in the array must be of the object-json type.

Return type: SETOF record.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT * FROM json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') AS x(a INT, b json, c BOOLEAN);
 a |      b      | c
---+-------------+---
 1 | {"d":"foo"} | t
 2 | {"d":"bar"} | f
(2 rows)

SELECT * FROM json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') AS x(a INT, b text, c BOOLEAN);
 a |  b  | c
---+-----+---
 1 | foo |
 2 | bar | t
(2 rows)

json_typeof(json)

Description: Checks the JSON type.

Return type: text.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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)

jsonb_object(text[])

Description: Constructs an object-jsonb from a text array. This is an overloaded function. When the input is a single text array, its length must be even, and the members are treated as alternately occurring key/value pairs.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
                   jsonb_object
---------------------------------------------------
 {"3": null, "a": "1", "b": "2", "d e f": "a b c"}
(1 row)

jsonb_object(text[], text[])

Description: When two text arrays are used, the first array is considered keys and the second array is considered values. The lengths of the two arrays must be equal. Keys cannot be null.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_object('{a,b,"a b c"}', '{a,1,1}');
            jsonb_object
------------------------------------
 {"a": "a", "b": "1", "a b c": "1"}
(1 row)

to_jsonb(anyment)

Description: Converts other types to their corresponding JSONB type.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT to_jsonb(1.1);
 to_jsonb
----------
 1.1
(1 row)

jsonb_agg

Description: Aggregates JSONB objects into a JSONB array.

Return type: jsonb.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT * FROM json_tbl_2;
 a |  b
---+-----
 1 | aaa
 1 | bbb
 2 | ccc
 2 | ddd
(4 rows)

SELECT a, jsonb_agg(b) FROM json_tbl_2 GROUP BY a ORDER BY a;
 a |   jsonb_agg
---+----------------
 1 | ["aaa", "bbb"]
 2 | ["ccc", "ddd"]
(2 rows)

jsonb_object_agg

Description: Aggregates key/value pairs into a JSON object.

Return type: jsonb.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT * FROM json_tbl_3;
 a |  b  | c
---+-----+----
 1 | aaa | 10
 1 | bbb | 20
 2 | ccc | 30
 2 | ddd | 40
(4 rows)
SELECT a, jsonb_object_agg(b, c) FROM json_tbl_3 GROUP BY a ORDER BY a;
 a |    jsonb_object_agg
---+------------------------
 1 | {"aaa": 10, "bbb": 20}
 2 | {"ccc": 30, "ddd": 40}
(2 rows)

jsonb_build_array( [VARIADIC "any"] )

Description: Constructs a JSON array that may contain heterogeneous types from a variable parameter list.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}','');
                               jsonb_build_array
-------------------------------------------------------------------------------
 ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1, 2, 3]}, null]
(1 row)

jsonb_build_object( [VARIADIC "any"] )

Description: Constructs a JSON object from a variable parameter list. The number of parameters must be even, with every two parameters forming a key-value pair. Note that keys cannot be null.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_build_object(1,2);
 jsonb_build_object
--------------------
 {"1": 2}
(1 row)

jsonb_strip_nulls(jsonb)

Description: Omits all object fields with null values. Other null values remain unchanged.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_strip_nulls('[{"f1":1,"f2":null},2,null,3]');
    jsonb_strip_nulls
-------------------------
 [{"f1": 1}, 2, null, 3]
(1 row)

jsonb_object_field(jsonb, text)

Description: Similar to the operator ->, it returns the value corresponding to the specified key in the object.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_object_field('{"a": {"b":"foo"}}','a');
 jsonb_object_field
--------------------
 {"b": "foo"}
(1 row)

jsonb_object_field_text(jsonb, text)

Description: Similar to the operator ->>, it returns the value corresponding to the specified key in the object.

Return type: text.

Example:

1
2
3
4
5

SELECT jsonb_object_field_text('{"a": {"b":"foo"}}','a');
 jsonb_object_field_text
-------------------------
 {"b": "foo"}
(1 row)

jsonb_array_element(array-jsonb, integer)

Description: Similar to the operator ->, it returns the element at the specified index in the array.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_array_element('[1,true,[1,[2,3]],null]',2);
 jsonb_array_element
---------------------
 [1, [2, 3]]
(1 row)

jsonb_array_element_text(array-jsonb, integer)

Description: Similar to the operator ->>, it returns the element at the specified index in the array.

Return type: text.

Example:

1
2
3
4
5

SELECT jsonb_array_element_text('[1,true,[1,[2,3]],null]',2);
 jsonb_array_element_text
--------------------------
 [1, [2, 3]]
(1 row)

jsonb_extract_path((jsonb, VARIADIC text[])

Description: Equivalent to the operator #>, it returns the value of the path pointed to by $2.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
 jsonb_extract_path
--------------------
 "stringy"
(1 row)

jsonb_extract_path_text((jsonb, VARIADIC text[])

Description: Equivalent to the operator #>>, it returns the value of the path pointed to by $2.

Return type: text.

Example:

1
2
3
4
5

SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
 jsonb_extract_path_text
-------------------------
 stringy
(1 row)

jsonb_extract((jsonb, VARIADIC text[])

Description: Takes any object-jsonb or array-jsonb type as input and returns the value at the path specified by $2. This function is only supported by clusters of version 9.1.0 or later.

Return type: SETOF jsonb.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT jsonb_extract('{"f2":{"f3":1},"f4":[{"f5":99},{"f6":"stringy"}]}','f2','f3');
 jsonb_extract
---------------
 1
(1 row)

SELECT jsonb_extract('{"f2":{"f3":1},"f4":[{"f5":99},{"f5":"stringy"}]}', 'f4','f5');
 jsonb_extract
---------------
 99
 "stringy"
(2 rows)

jsonb_extract_text((jsonb, VARIADIC text[])

Description: Takes any object-jsonb or array-jsonb type as input and returns the value at the path specified by $2. This function is only supported by clusters of version 9.1.0 or later.

Return type: SETOF text.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT jsonb_extract_text('{"f2":{"f3":1},"f4":[{"f5":99},{"f6":"stringy"}]}','f2','f3');
 jsonb_extract_text
--------------------
 1
(1 row)

SELECT jsonb_extract_text('{"f2":{"f3":1},"f4":[{"f5":99},{"f5":"stringy"}]}', 'f4','f5');
 jsonb_extract_text
--------------------
 99
 stringy
(2 rows)

jsonb_array_elements(array-jsonb)

Description: Splits an array, returning each element as a row.

Return type: jsonb.

Example:

1
2
3
4
5
6
7
8

SELECT jsonb_array_elements('[1,true,[1,[2,3]],null]');
 jsonb_array_elements
----------------------
 1
 true
 [1, [2, 3]]
 null
(4 rows)

jsonb_array_elements_text(array-jsonb)

Description: Splits an array, returning each element as a row.

Return type: text.

Example:

1
2
3
4
5
6
7
8

SELECT * FROM jsonb_array_elements_text('[1,true,[1,[2,3]],null]');
    value
-------------
 1
 true
 [1, [2, 3]]

(4 rows)

jsonb_array_length(array-jsonb)

Description: Returns the array length.

Return type: integer.

Example:

1
2
3
4
5

SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4,null]');
 jsonb_array_length
--------------------
          6
(1 row)

jsonb_object_keys(object-jsonb)

Description: Returns all top-level keys in the object.

Return type: SETOF text.

Example:

1
2
3
4
5
6

SELECT jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
 jsonb_object_keys
-------------------
 f1
 f2
(2 rows)

jsonb_each(object-jsonb)

Description: Splits each key-value pair of the object into one row with two columns.

Return type: setof(key text, value jsonb).

Example:

1
2
3
4
5
6
7

SELECT * FROM jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
 key |   value
-----+-----------
 f1  | [1, 2, 3]
 f2  | {"f3": 1}
 f4  | null
(3 rows)

jsonb_each_text(object-jsonb)

Description: Splits each key-value pair of the object into one row with two columns.

Return type: setof(key text, value text).

Example:

1
2
3
4
5
6
7

SELECT * FROM jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
 key |   value
-----+-----------
 f1  | [1, 2, 3]
 f2  | {"f3": 1}
 f4  |
(3 rows)

jsonb_populate_record(anyelement, object-jsonb [, bool])

Description: $1 must be a parameter of a composite type. It splits each key-value pair in object-json, using the key as the column name to match and find the corresponding column name in $1, then fills it into the format of $1.

Return type: anyelement.

Example:

1
2
3
4
5

SELECT * FROM jsonb_populate_record(null::jpop,'{"a":"blurfl","x":43.2}');
   a    | b | c
--------+---+---
 blurfl |   |
(1 row)

jsonb_populate_record_set(anyelement, array-jsonb [, bool])

Description: Perform the operation described above on each element of the $2 array by referring to the functions json_populate_record and jsonb_populate_record. Thus, each element of the $2 array must be of the object-json type.

Return type: setof anyelement.

Example:

1
2
3
4
5
6

SELECT * FROM json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]');
 a | b | c
---+---+---
 1 | 2 |
 3 | 4 |
(2 rows)

jsonb_to_record(object-json)

Description: As with all functions that return a record, the caller must explicitly define the structure of the record with an AS clause. It splits and reassembles the key-value pairs of object-json, using the key as the column name to match and fill the structure of the record specified by the AS clause.

Return type: record.

Example:

1
2
3
4
5

SELECT * FROM jsonb_to_record('{"a":1,"b":"foo","c":"bar"}'::jsonb) as x(a int, b text, d text);
 a |  b  | d
---+-----+---
 1 | foo |
(1 row)

jsonb_to_recordset(array-json)

Description: Executes the preceding function on each element within the array by referring to the function jsonb_to_record. Hence, every element in the array must be of the object-jsonb type.

Return type: SETOF record.

Example:

1
2
3
4
5
6

SELECT * FROM jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') AS x(a INT, b text, c boolean);
 a |  b  | c
---+-----+---
 1 | foo |
 2 | bar | t
(2 rows)

jsonb_typeof(jsonb)

Description: Checks the JSONB type.

Return type: text.

Example:

1
2
3
4
5

SELECT jsonb_typeof(to_jsonb(1.1));
 jsonb_typeof
--------------
 number
(1 row)

jsonb_ne(jsonb, jsonb)

Description: Same as the operator <>, which compares the size of two values.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_ne('{"a":1, "b":2}'::jsonb, '{"a":1, "b":3}'::jsonb);
 jsonb_ne
----------
 t
(1 row)

jsonb_lt(jsonb, jsonb)

Description: Same as the operator <, which compares the size of two values.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_lt('{"a":1, "b":2}'::jsonb, '{"a":1, "b":3}'::jsonb);
 jsonb_lt
----------
 t
(1 row)

jsonb_gt(jsonb, jsonb)

Description: Same as the operator >, which compares the size of two values.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_gt('{"a":1, "b":2}'::jsonb, '{"a":1, "b":3}'::jsonb);
 jsonb_gt
----------
 f
(1 row)

jsonb_le(jsonb, jsonb)

Description: Same as the operator <=, which compares the size of two values.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_le('["a", "b"]', '{"a":1, "b":2}');
 jsonb_le
----------
 t
(1 row)

jsonb_ge(jsonb, jsonb)

Description: Same as the operator >=, which compares the size of two values.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
 jsonb_ge
----------
 f
(1 row)

jsonb_eq(jsonb, jsonb)

Description: Same as the operator =, which compares the size of two values.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
 jsonb_eq
----------
 f
(1 row)

jsonb_cmp(jsonb, jsonb)

Description: Compares sizes. A positive number indicates greater than, a negative number indicates less than, and 0 indicates equality.

Return type: integer.

Example:

1
2
3
4
5

SELECT jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
 jsonb_cmp
-----------
 -1
(1 row)

jsonb_exists(jsonb, text)

Description: Same as the operator ?, which checks if the string $2 exists at the top level of $1 in the form of key\elem\scalar.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_exists('["1",2,3]', '1');
 jsonb_exists
--------------
 t
(1 row)

jsonb_exists_any(jsonb, text[])

Description: Same as the operator ?|, which checks if any element in the string array $2 exists at the top level of $1 in the form of key\elem\scalar.

Return type:

Example:

1
2
3
4
5

SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}');
 jsonb_exists_any
------------------
 t
(1 row)

jsonb_exists_all(jsonb, text[])

Description: Same as the operator ?&, which checks if all elements in the string array $2 exist at the top level of $1 in the form of key\elem\scalar.

Return type:

bool

Example:

1
2
3
4
5

SELECT jsonb_exists_all('["1","2",3]', '{1, 2}');
 jsonb_exists_all
------------------
 t
(1 row)

jsonb_contained(jsonb, jsonb)

Description: Same as the operator <@, which determines if all elements in $1 exist at the top level of $2.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_contained('[1,2,3]', '[1,2,3,4]');
 jsonb_contained
-----------------
 t
(1 row)

jsonb_contains(jsonb, jsonb)

Description: Same as the operator @>, which determines if all top-level elements in $1 are contained within all elements in $2.

Return type: Boolean.

Example:

1
2
3
4
5

SELECT jsonb_contains('{"a":1, "b":2, "c":3}'::jsonb, '{"a":1}');
 jsonb_contains
-----------------
 t
(1 row)

jsonb_concat(jsonb, jsonb)

Description: Concatenates two JSONB objects into one JSONB.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_concat('{"a":1, "b":2}'::jsonb, '{"c":3, "d":4}'::jsonb);
           jsonb_concat
----------------------------------
 {"a": 1, "b": 2, "c": 3, "d": 4}
(1 row)

jsonb_delete(jsonb, text)

Description: Deletes the key-value pair corresponding to the key in JSONB.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_delete('{"a":1, "b":2}'::jsonb, 'a');
 jsonb_delete
--------------
 {"b": 2}
(1 row)

jsonb_delete_idx(jsonb, text)

Description: Deletes the element corresponding to an array index.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_delete_idx('[0,1,2,3,4]'::jsonb, 2);
 jsonb_delete_idx
------------------
 [0, 1, 3, 4]
(1 row)

jsonb_delete_array(jsonb, VARIADIC text[])

Description: Deletes multiple elements from a JSONB array.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_delete_array('["a", "b", "c"]'::jsonb , 'a', 'b');
 jsonb_delete_array
--------------------
 ["c"]
(1 row)

jsonb_delete_path(jsonb, text[])

Description: Deletes elements at a specified path in a JSONB array.

Return type: jsonb.

Example:

1
2
3
4
5

SELECT jsonb_delete_path('{"a":{"b":{"c":1, "d":2}}, "e":3}'::jsonb , array['a', 'b']);
 jsonb_delete_path
-------------------
 {"a": {}, "e": 3}
(1 row)

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

Description: Returns target, where the part specified by path is replaced by new_value, or adds new_value if create_missing is true (true by default) and the item specified by path does not exist. As with path-oriented operators, negative integers in the path count from the end of the JSON array.

Return type: jsonb.

Example:

1
2
3
4
5

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

jsonb_pretty(jsonb)

Description: Returns formatted JSON text with indentation.

Return type: jsonb.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT jsonb_pretty('{"a":{"b":{"c":1, "d":2}}, "e":3}'::jsonb);
    jsonb_pretty
---------------------
 {                  +
     "a": {         +
         "b": {     +
             "c": 1,+
             "d": 2 +
         }          +
     },             +
     "e": 3         +
 }
(1 row)

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

Description: Returns target and inserts new_value. If the target part specified by path is in a JSONB array, new_value is inserted before the target or after it if insert_after is true (false by default). If the target part specified by path is in a JSONB object, new_value is inserted only if the target does not exist. As with path-oriented operators, negative integers in the path count from the end of the JSON array.

Return type: jsonb.

Example:

1
2
3
4
5

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

ts_headline([ config regconfig, ] document jsonb, query tsquery [, options text ])

Description: Highlights JSONB search results.

Return type: jsonb.

Example:

1
2
3
4
5
6

SELECT ts_headline('english', '[{"id":9928,"user_id":4562,"user_name":"9LOHR4","create_time":"2021-06-22T16:28:16.504518+08:00"}, {"id":9959,"user_id":5524,"user_name":"YID07D","create_time":"2021-06-22T16:28:16.557228+08:00"}, {"id":9962,"user_id":7991,"user_name":"7C6QOM","create_time":"2021-06-22T16:28:16.56234+08:00"}]'::jsonb,
 to_tsquery('english', '9LOHR4'), 'StartSel = <, StopSel = >');
                                                                                                                                                         ts_headline                                                                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"id": 9928, "user_id": 4562, "user_name": "<9LOHR4>", "create_time": "2021-06-22T16:28:16.504518+08:00"}, {"id": 9959, "user_id": 5524, "user_name": "YID07D", "create_time": "2021-06-22T16:28:16.557228+08:00"}, {"id": 9962, "user_id": 7991, "user_name": "7C6QOM", "create_time": "2021-06-22T16:28:16.56234+08:00"}]
(1 row)

json_to_tsvector(config regconfig, ] json, jsonb)

Description: Converts JSON format to the tsvector format for supporting full-text search.

Return type: jsonb.

1
2
3
4
5

SELECT json_to_tsvector('{"a":1, "b":2, "c":3}'::json, to_jsonb('key'::text));
 json_to_tsvector
------------------
 'b':2 'c':4
(1 row)