JSON and JSONB data types

Limits

Overview of the JSON and JSONB data types

Operators supported by JSON and JSONB

Additional JSONB operators

JSON creation functions

JSON processing functions

json_object_keys
------------------
 f1
 f2

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"}

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

   value
-----------
 1
 true
 [2,false]

 value
-----------
 foo
 bar

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")

 a |  b
---+-----
 1 | foo
 2 |

[
    {
        "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.