HomeMogDBMogDB StackUqbar
v3.0

Documentation:v3.0

Supported Versions:

Other Versions:

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

img 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
      json_object_keys
     ------------------
      f1
      f2
      f1
     (3 rows)
  • JSONB deduplication operations:

    MogDB=# 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:

    MogDB=# 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)
    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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 thejson_to_record function. Therefore, each element in the array must be object-json.

    Return type: SETOF record

    For example:

    MogDB=# select * from json_to_recordset(
    mogdb(#   '[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',
    mogdb(#   false
    mogdb(# ) 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:

    MogDB=# 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)
    MogDB=# 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:

    MogDB=# select * from classes;
     name | score
     -----+-------
     A    |     2
     A    |     3
     D    |     5
     D    |
     (4 rows)
    MogDB=# 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:

    MogDB=# select * from classes;
     name | score
     -----+-------
     A    |     2
     A    |     3
     D    |     5
     D    |
     (4 rows)
    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# 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:

    MogDB=# select to_json('{1,5}'::text[]);
       to_json
     -----------
      ["1","5"]
     (1 row)
Copyright © 2011-2024 www.enmotech.com All rights reserved.