This topic describes the syntax supported by the JSON and JSONB data types and how to use these data types.

Limits

Hologres supports the JSON and JSONB data types. Take note of the following rules when you use them:
  • Only Hologres V0.9 and later support the JSON and JSONB data types. To upgrade an instance, submit a ticket or join the Hologres DingTalk group for technical support.
  • Only Hologres V1.1 and later support GIN indexes for fields of the JSONB data type. To upgrade an instance, submit a ticket or join the Hologres DingTalk group for technical support.
  • Hologres does not support the following JSON-specific functions: json_each, jsonb_each, json_each_text, jsonb_each_text, json_extract_path, jsonb_extract_path, jsonb_agg, and jsonb_to_record.
  • You cannot write data of the JSONB type from Flink to Hologres.

Overview of the JSON and JSONB data types

The major practical difference between these two data types is their efficiency.
  • Data of the JSON type is stored in the form of text. Such data can be written at a fast pace, but data reads are time-consuming. The JSON data type stores an exact copy of the input text. The processing functions must reparse the copy during each execution. In addition, insignificant characters such as spaces may exist in data of the JSON type to comply with the semantic constraints of the input text. If a JSON object within a value contains the same key more than once, all the key-value pairs are kept. The processing functions consider the last value as the operative one.
  • Data of the JSONB type is stored as binary data. Such data can be read at a fast pace, but data writes are time-consuming. Data of the JSONB type is stored in a decomposed binary format. Compared with data of the JSON type, data of the JSONB type takes more time to be written due to the additional conversion process. However, the processing of JSONB data takes less time because no reparsing is required. Spaces, the order of object keys, and duplicate object keys do not exist in JSONB data. If duplicate object keys are specified in the input text, only the last value is retained.
Note Keys must be enclosed in double quotation marks (").

Operators supported by JSON and JSONB

The JSON and JSONB data types are used to store JSON data. Such data can also be stored as the TEXT type. The JSON and JSONB data types have the advantage of enforcing that each stored value is valid based on the JSON rules. The following table describes the operators that are supported by these two data types.
Operator Right operand type Description Example Result
-> int Obtains a JSON array element with indexes starting from zero and negative integers counted from the end. select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text Obtains a JSON object field based on a key. select '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int Obtains a JSON array element as text. select '[1,2,3]'::json->>2 3
->> text Obtains a JSON object field as text. select '{"a":1,"b":2}'::json->>'b' 2
#> text[] Obtains a JSON object from a specified path. select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c":"foo"}
#>> text[] Obtains a JSON object as text from a specified path. select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

Additional JSONB operators

The following table describes the operators that are supported by the JSONB data type.
Operator Right operand type Description Example Result
@> jsonb Specifies whether the left JSON value contains the right JSON value. select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb true
<@ jsonb Specifies whether the left JSON value is contained within the right JSON value. select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb true
? text Specifies whether a string exists as a key or as a string within the JSON value. select '{"a":1, "b":2}'::jsonb ? 'b' true
?| text[] Specifies whether one of the right array strings exists as a key or as a string within the JSON value. select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] true
?& text[] Specifies whether all of the right array strings exist as keys or as strings within the JSON value. select '["a", "b"]'::jsonb ?& array['a', 'b'] true
|| jsonb Concatenates two JSONB values into a new JSONB value. select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb ["a", "b", "c", "d"]
- text Deletes a key-value pair or string element from the left operand. Key-value pairs are matched based on the values. select '{"a": "b"}'::jsonb - 'a' {}
- text[] Deletes multiple key-value pairs or string elements from the left operand. Key-value pairs are matched based on the values. select '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] {}
- integer Deletes an array element that has a specified index. Negative integers are counted from the end. An error is returned if the top-level container is not an array. select '["a", "b"]'::jsonb - 1 ["a"]
#- text[] Deletes a field or an element with a specified path. For JSON arrays, negative integers are counted from the end. select '["a", {"b":1}]'::jsonb #- '{1,b}' ["a", {}]

JSON creation functions

The following table describes the functions that are used to create JSON values.
Function Description Example Result
to_json(anyelement) Returns a value as a valid JSON object. Arrays and composites are recursively converted to arrays and objects. If a cast function is provided, that cast function is invoked to convert the input value into a JSON object. Otherwise, a scalar value is generated. If the scalar value is not a number, BOOLEAN value, or null value, the scalar value is represented by JSON text that makes it a valid JSON string. select to_json('Fred said "Hi."'::text) "Fred said \"Hi.\""
to_jsonb(anyelement)
array_to_json(anyarray [, pretty_bool]) Returns an array as a JSON array. If you enter a multidimensional array, a JSON array of arrays is returned. If the value of the pretty_bool parameter is true, line feeds are added between dimension-1 elements. select array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
json_build_array(VARIADIC "any") Builds a JSON array that may contain heterogeneous data based on a list of variable arguments. select json_build_array(1,2,'3',4,5) [1, 2, "3", 4, 5]
jsonb_build_array(VARIADIC "any")
json_build_object(VARIADIC "any") Builds a JSON object based on a list of variable arguments. The argument list consists of alternating keys and values. select json_build_object('foo',1,'bar',2) {"foo": 1, "bar": 2}
jsonb_build_object(VARIADIC "any")
json_object(text[]) Builds a JSON object based on a text array. The array can contain one dimension that has an even number of members. The members are taken as alternating key-value pairs. Alternatively, the array can contain two dimensions and each inner array has two elements, which are taken as a key-value pair. select json_object('{a, 1, b, "def", c, 3.5}'); {"a": "1", "b": "def", "c": "3.5"}
jsonb_object(text[]) select jsonb_object('{a, 1, b, "def", c, 3.5}'); {"a": "1", "b": "def", "c": "3.5"}
json_object(keys text[], values text[]) Obtains key-value pairs based on two separate arrays. In other respects, the result is in the same format as that of a single argument. select json_object('{a, b}', '{1,2}') {"a": "1", "b": "2"}
jsonb_object(keys text[], values text[])

JSON processing functions

The following table describes the functions used to process JSON values.
Function Return value type Description Example Result
json_array_length(json) int Returns the number of elements in the outermost JSON array. select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 5
jsonb_array_length(jsonb)
json_extract_path_text(from_json json, VARIADIC path_elems text[]) text Returns the JSON value specified by the path_elems parameter as JSON text. This function is equivalent to the #>> operator. select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') foo
jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
json_object_keys(json) setof text Returns a set of keys in the outermost JSON object. select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
json_object_keys
------------------
 f1
 f2
jsonb_object_keys(jsonb)
json_populate_record(base anyelement, from_json json) anyelement Expands the objects in the from_json parameter into a row with columns that match the record type defined by the base parameter.
begin;
create table 
myrowtype( a text, b text, c text);commit;
select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | {"d": 4, "e": "a b c"}
jsonb_populate_record(base anyelement, from_json jsonb)
json_populate_recordset(base anyelement, from_json json) setof anyelement Expands the outermost array of objects in the from_json parameter into a set of rows with columns that match the record type defined by the base parameter.
begin;
create table 
myrowtype(a text,b text);
commit;
select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
 a | b
---+---
 1 | 2
 3 | 4
jsonb_populate_recordset(base anyelement, from_json jsonb)
json_array_elements(json) setof json Expands a JSON array into a set of JSON values. select * from json_array_elements('[1,true, [2,false]]')
   value
-----------
 1
 true
 [2,false]
jsonb_array_elements(jsonb) setof jsonb
json_array_elements_text(json) setof text Expands a JSON array into a set of text values. select * from json_array_elements_text('["foo", "bar"]')
 value
-----------
 foo
 bar
jsonb_array_elements_text(jsonb)
json_typeof(json) text Returns the type of the outermost JSON value as a text string. Possible types include OBJECT, ARRAY, STRING, NUMBER, BOOLEAN, and NULL. select json_typeof('-123.4') number
jsonb_typeof(jsonb)
json_to_record(json) record Builds an arbitrary record based on a JSON object. As required by those functions that return records, you must explicitly define the structure of the record by using an AS clause. create table jpop (a text, b int, c timestamp);select * from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123, "c": "2020-01-01 12:00:00.000"}}'::json) as t(a int, b json, c text, x int, ca char(5)[], ia int[][], r jpop);
a |        b        | c | x |        ca         |      ia       |                r

---+-----------------+---+---+-------------------+---------------+---------------------------------

1 | {"c":16, "d":2} |   | 8 | {"1 2  ","3    "} | {{1,2},{3,4}} | (aaa,123,"2020-01-01 12:00:00")
jsonb_to_record(jsonb)
json_to_recordset(json) setof record Builds an arbitrary set of records based on a JSON array of objects. As required by those functions that return records, you must explicitly define the structure of the record by using an AS clause. select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
 a |  b
---+-----
 1 | foo
 2 |
jsonb_to_recordset(jsonb)
json_strip_nulls(from_json json) json Returns the objects in the from_json parameter. Object fields that have null values are omitted. Other null values are retained. select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') [{"f1":1},2,null,3]
jsonb_strip_nulls(from_json jsonb) jsonb
jsonb_set(target jsonb, path text[], new_value jsonb[,create_missing boolean]) jsonb Returns the objects in the target parameter. The section specified by the path parameter is replaced by the value of the new_value parameter. If the value of the create_missing parameter is true, which is the default value, and the item specified by the path parameter does not exist, the value of the new_value parameter is inserted. As required by the path-oriented operators, negative integers that appear in the value of the path parameter are counted from the end of JSON arrays. select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false); [{"f1":[2,3,4],"f2":null},2,null,3]
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean]) jsonb Returns the objects in the target parameter with the value of the new_value parameter inserted. The section specified by the path parameter may be in a JSONB array. In this case, if the value of the insert_after parameter is false, which is the default value, the value of the new_value parameter is inserted before the value of the target parameter. Otherwise, the value of the new_value parameter is inserted after the value of the target parameter. The section specified by the path parameter may be in a JSONB object. In this case, the value of the new_value parameter is inserted only if the value of the target parameter does not exist. As required by the path-oriented operators, negative integers that appear in the value of the path parameter are counted from the end of JSON arrays. select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') {"a": [0, "new_value", 1, 2]}
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) {"a": [0, 1, "new_value", 2]}
jsonb_pretty(from_json jsonb) text Returns the objects in the from_json parameter as indented JSON text. select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

For more information about the JSON and JSONB data types, see JSON functions and operators.

JSONB indexing

In Hologres V1.1 and later, GIN indexes can be created on fields of the JSONB type. The following section describes the syntax used to create GIN indexes and provides examples.

  • Syntax
    You can create GIN and B-tree indexes on fields of the JSONB type. In most cases, GIN indexes are used. The following statements show the syntax used to create GIN indexes by using different operator classes:
    • Use the default jsonb_ops operator class to create an index
      CREATE INDEX idx_name ON table_name USING gin (idx_col);
    • Use the jsonb_path_ops operator class to create an index
      CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_path_ops);
    • Use the jsonb_holo_path_ops operator class to create an index
      CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_holo_path_ops);
    A jsonb_ops GIN index creates independent index items for each key and value in the data, whereas a jsonb_path_ops GIN index creates index items only for each value in the data. jsonb_holo_path_ops is an operator class provided by Hologres and frees you from the reviews of retrieved data.
  • Examples

    You can use the jsonb_ops, jsonb_path_ops, or jsonb_holo_path_ops operator class to create a GIN index on a field of the JSONB type. The following examples show the detailed information:

    • Use the jsonb_ops operator class
      1. Execute the following statement to create a table:
        CREATE TABLE IF NOT EXISTS json_table 
        (
            id INT
            ,j jsonb
        );
      2. Execute the following statement to use the jsonb_ops operator class to create an index:
        CREATE INDEX index_json on json_table USING GIN(j);
      3. Insert the following data into the table:
        INSERT INTO json_table VALUES
        (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ;
        (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ;
      4. Execute the following statement to query data:
        SELECT  *
        FROM    json_table
        WHERE   j ? 'key1'
        ;
      5. The following results are returned:
         id |                        j                        
        ----+-------------------------------------------------
          1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
          1 | "key1"
        (2 rows)
      6. Use the EXPLAIN command to query the execution plan. The following results are returned:
                                                QUERY PLAN                                         
        -------------------------------------------------------------------------------------------
         Gather  (cost=0.00..3.17 rows=1 width=43)
           ->  Exchange (Gather Exchange)  (cost=0.00..3.17 rows=1 width=43)
                 ->  Decode  (cost=0.00..3.17 rows=1 width=43)
                       ->  Bitmap Heap Scan on json_table  (cost=0.00..3.07 rows=1 width=43)
                             Recheck Cond: (j ? 'key1'::text)
                             ->  Bitmap Index Scan on index_json  (cost=0.00..0.00 rows=0 width=0)
                                   Index Cond: (j ? 'key1'::text)
         Optimizer: HQO version 0.10.0
        (8 rows)
        As shown in the preceding results, the execution plan contains the Index Scan step. This indicates that an index is used during the query.
    • Use the jsonb_path_ops operator class
      1. Execute the following statement to create a table:
        CREATE TABLE IF NOT EXISTS json_table 
        (
            id INT
            ,j jsonb
        );
      2. Execute the following statement to use the jsonb_path_ops operator class to create an index:
        CREATE INDEX index_json on json_table USING GIN(j jsonb_path_ops);
      3. Insert the following data into the table:
        INSERT INTO json_table (
            SELECT 
                i, 
                ('{
                    "key1": "'||i||'"
                    ,"key2": "'||i%100||'"
                    ,"key3": "'||i%1000 ||'"
                    ,"key4": "'||i%10000||'"
                    ,"key5": "'||i%100000||'"
                }')::jsonb 
            FROM generate_series(1, 1000000) i
        ) ;
      4. Execute the following statement to query data based on the condition '{"key1": "10"}':
        SELECT  *
        FROM    json_table
        WHERE   j @> '{"key1": "10"}'::JSONB 
        ;
      5. The following results are returned:
         id |                                   j                                    
        ----+------------------------------------------------------------------------
         10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
        (1 row)
      6. Use the EXPLAIN command to query the execution plan. The following results are returned:
                                                   QUERY PLAN                                            
        -------------------------------------------------------------------------------------------------
         Gather  (cost=0.00..34709320.40 rows=400000 width=88)
           ->  Exchange (Gather Exchange)  (cost=0.00..34709240.95 rows=400000 width=88)
                 ->  Decode  (cost=0.00..34709240.92 rows=400000 width=88)
                       ->  Bitmap Heap Scan on json_table  (cost=0.00..34709240.00 rows=400000 width=88)
                             Recheck Cond: (j @> '{"key1": "10"}'::jsonb)
                             ->  Bitmap Index Scan on index_json  (cost=0.00..0.00 rows=0 width=0)
                                   Index Cond: (j @> '{"key1": "10"}'::jsonb)
         Optimizer: HQO version 0.10.0
        (8 rows)
        As shown in the preceding results, the execution plan contains the Index Scan step. This indicates that an index is used during the query.
    • Use the jsonb_holo_path_ops operator class

      Native GIN operator classes for JSONB provided by PostgreSQL cannot be used to retrieve accurate data. Reviews are required after data is retrieved. Query performance may fail to be improved even if an index is used. To resolve this issue, Hologres provides an operator class that frees you from reviews. By default, if you do not specify an operator class, the jsonb_holo_path_ops operator class is used.

      1. Execute the following statement to create a table:
        CREATE TABLE IF NOT EXISTS json_table 
        (
            id INT
            ,j jsonb
        );
      2. Execute the following statement to use the jsonb_holo_path_ops operator class to create an index:
        -- Use the default jsonb_holo_path_ops operator class.
        CREATE INDEX index_json on json_table USING GIN(j);
        -- Specify an operator class. This statement has the same effect as the preceding statement.
        CREATE INDEX index_json on json_table USING GIN(j jsonb_holo_path_ops);
      3. Insert the following data into the table:
        INSERT INTO json_table (
            SELECT 
                i, 
                ('{
                    "key1": "'||i||'"
                    ,"key2": "'||i%100||'"
                    ,"key3": "'||i%1000 ||'"
                    ,"key4": "'||i%10000||'"
                    ,"key5": "'||i%100000||'"
                }')::jsonb 
            FROM generate_series(1, 1000000) i
        ) ;
      4. Execute the following statement to query data based on the condition '{"key1": "10"}':
        SELECT  *
        FROM    json_table
        WHERE   j @> '{"key1": "10"}'::JSONB 
        ;
      5. The following results are returned:
         id |                                   j                                    
        ----+------------------------------------------------------------------------
         10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
        (1 row)
      6. Use the EXPLAIN command to query the execution plan. The following results are returned:
        QUERY PLAN
        Gather  (cost=0.00..39038928.99 rows=400000 width=88)
          ->  Exchange (Gather Exchange)  (cost=0.00..39038843.49 rows=400000 width=88)
                ->  Decode  (cost=0.00..39038843.37 rows=400000 width=88)
                      ->  Bitmap Heap Scan on json_table  (cost=0.00..39038840.00 rows=400000 width=88)
        "                    Recheck Cond: (j @> '{"key1": "10"}'::jsonb)"
                            ->  Bitmap Index Scan on index_json  (cost=0.00..0.00 rows=0 width=0)
        "                          Index Cond: (j @> '{"key1": "10"}'::jsonb)"
        Optimizer: HQO version 0.10.0
        As shown in the preceding results, the execution plan contains the Index Scan step. This indicates that an index is used during the query.