实时数仓Hologres：JSON和JSONB类型

使用限制

JSON和JSONB类型概述

JSON和JSONB的操作符

额外的JSONB操作符

JSON创建函数

JSON处理函数

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

[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

DROP TABLE IF EXISTS t;

CREATE TABLE t (
    k int PRIMARY KEY,
    class int NOT NULL,
    v text NOT NULL
);

INSERT INTO t (k, class, v)
SELECT
    (1 + s.v),
    CASE (s.v) < 3
    WHEN TRUE THEN
        1
    ELSE
        2
    END,
    chr(97 + s.v)
FROM
    generate_series(0, 5) AS s (v);

SELECT
    class,
    jsonb_agg(v ORDER BY v DESC) FILTER (WHERE v <> 'b') AS "jsonb_agg",
    jsonb_object_agg(v, k ORDER BY v DESC) FILTER (WHERE v <> 'e') AS "jsonb_object_agg(v, k)"
FROM
    t
GROUP BY
    class;

 class |    jsonb_agg    |  jsonb_object_agg(v, k)
-------+-----------------+--------------------------
     1 | ["c", "a"]      | {"a": 1, "b": 2, "c": 3}
     2 | ["f", "e", "d"] | {"d": 4, "f": 6}

DROP TABLE IF EXISTS t;

CREATE TABLE t (
    k int PRIMARY KEY,
    class int NOT NULL,
    v text NOT NULL
);

INSERT INTO t (k, class, v)
SELECT
    (1 + s.v),
    CASE (s.v) < 3
    WHEN TRUE THEN
        1
    ELSE
        2
    END,
    chr(97 + s.v)
FROM
    generate_series(0, 5) AS s (v);

SELECT
    class,
    jsonb_agg(v ORDER BY v DESC) FILTER (WHERE v <> 'b') AS "jsonb_agg",
    jsonb_object_agg(v, k ORDER BY v DESC) FILTER (WHERE v <> 'e') AS "jsonb_object_agg(v, k)"
FROM
    t
GROUP BY
    class;

 class |    jsonb_agg    |  jsonb_object_agg(v, k)
-------+-----------------+--------------------------
     1 | ["c", "a"]      | {"a": 1, "b": 2, "c": 3}
     2 | ["f", "e", "d"] | {"d": 4, "f": 6}

DROP TABLE IF EXISTS test_json;

CREATE TABLE test_json (
    id int,
    json_strings text
);

INSERT INTO test_json
    VALUES (1, '{"a":2}'), (2, '{"a":{"b":{"c":1}}}'), (3, '{"a": [1,2,"b"]}');

INSERT INTO test_json
    VALUES (4, '{{}}'), (5, '{1:"a"}'), (6, '[1,2,3]');

SELECT
    id,
    json_strings,
    is_valid_json (json_strings)
FROM
    test_json
ORDER BY
    id;

id | json_strings        | is_valid_json
---+---------------------+--------------
 0 | {"a":2}             | true
 2 | {"a":{"b":{"c":1}}} | true
 4 | {"a": [1,2,"b"]}    | true
 6 | {{}}                | false
 8 | {1:"a"}             | false
10 | [1,2,3]             | true

更多关于JSON类型的用法，请参见JSON函数和操作符。

JSONB索引

从Hologres V1.1版本开始，支持JSONB索引，如下为您介绍在JSONB类型字段上建GIN索引的语法解释和使用示例。

• 语法解释
  JSONB类型支持GIN，BTree索引。一般情况下，会在JSONB类型字段上建GIN索引，建GIN索引存在三种方式，语法分别如下。

  • 使用默认的jsonb_ops操作符创建索引

    CREATE INDEX idx_name ON table_name USING gin (idx_col);

  • 使用jsonb_path_ops操作符创建索引

    CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_path_ops);

  • 使用jsonb_holo_path_ops操作符创建索引

    CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_holo_path_ops);

  三种方式的区别为：在jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项的；而jsonb_path_ops则只为每个value创建一个索引项；jsonb_holo_path_ops为Hologres全新的操作符号，可以省去检索数据后recheck的动作。
• 使用示例
  • 原生PostgreSQL操作符
    • 创建jsonb_ops操作符号索引
      1. 创建表的SQL语句如下。

         DROP TABLE IF EXISTS json_table;
         CREATE TABLE IF NOT EXISTS json_table
         (
             id INT
             ,j jsonb
         );

      2. 创建jsonb_ops操作符号索引的SQL语句如下。

         CREATE INDEX index_json on json_table USING GIN(j);

      3. 在表中插入数据的SQL语句如下。

         INSERT INTO json_table VALUES
         (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ,
         (1, '{"key1": 1}'),
         (2, '{"key2": [1, 2], "key3": {"a": "b"}}') ;

      4. 筛选包含数据的SQL语句如下。

         SELECT  *
         FROM    json_table
         WHERE   j ? 'key1'
         ;

      5. 执行结果如下。

          id |                        j                        
         ----+-------------------------------------------------
           1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
           1 | "key1"
         (2 rows)

      6. 使用explain命令查看执行计划如下。

                                                 QUERY PLAN
         -------------------------------------------------------------------------------------------
          Gather  (cost=0.00..12.36 rows=1 width=66)
            ->  Exchange (Gather Exchange)  (cost=0.00..12.36 rows=1 width=66)
                  ->  Decode  (cost=0.00..12.36 rows=1 width=66)
                        ->  Bitmap Heap Scan on json_table  (cost=0.00..12.26 rows=1 width=66)
                              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 1.1.0
         (8 rows)

         执行计划中出现了Index Scan步骤，表明查询过程使用了索引。
    • 创建jsonb_path_ops操作符号索引
      1. 创建表的SQL语句如下。

         DROP TABLE IF EXISTS json_table;
         CREATE TABLE IF NOT EXISTS json_table
         (
             id INT
             ,j jsonb
         );

      2. 创建jsonb_path_ops操作符号索引的SQL语句如下。

         CREATE INDEX index_json on json_table USING GIN(j jsonb_path_ops);

      3. 插入数据的SQL语句如下。

         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. 筛选包含'{"key1": "10"}'数据的SQL语句如下。

         SELECT  *
         FROM    json_table
         WHERE   j @> '{"key1": "10"}'::JSONB 
         ;

      5. 执行结果如下。

          id |                                   j                                    
         ----+------------------------------------------------------------------------
          10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
         (1 row)

      6. 使用explain命令查看执行计划如下。

                                                 QUERY PLAN
         -------------------------------------------------------------------------------------------
          Gather  (cost=0.00..0.26 rows=1000 width=12)
            ->  Exchange (Gather Exchange)  (cost=0.00..0.23 rows=1000 width=12)
                  ->  Decode  (cost=0.00..0.23 rows=1000 width=12)
                        ->  Bitmap Heap Scan on json_table  (cost=0.00..0.13 rows=1000 width=12)
                              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 1.1.0
         (8 rows)

         执行计划中出现了Index Scan步骤，表明查询过程使用了索引。
  • Hologres操作符

    由于原生PostgreSQL的JSONB的GIN索引是非精确的索引，所以检索数据后需要进行recheck动作。最终导致创建索引后性能不一定提升。针对上述情况，Hologres实现了一种新的ops_class，可以省去recheck的动作，且若不指定索引操作符，系统会默认使用该操作符，具体使用方式如下。

    其中jsonb_holo_ops对应jsonb_ops，支持?, ?|, ?&, @>的过滤操作。其中jsonb_holo_path_ops对应jsonb_path_ops，仅支持@>的过滤操作。

    • 创建jsonb_holo_ops操作符号索引
      1. 创建表的SQL语句如下。

         DROP TABLE IF EXISTS json_table;
         CREATE TABLE IF NOT EXISTS json_table
         (
             id INT
             ,j jsonb
         );

      2. 创建jsonb_holo_ops操作符号索引的SQL语句如下。

         -- 创建索引，使用jsonb_holo_ops操作符
         CREATE INDEX index_json on json_table USING GIN(j jsonb_holo_ops);

      3. 插入数据的SQL语句如下。

         INSERT INTO json_table VALUES
         (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ,
         (1, '{"key1": 1}'),
         (2, '{"key2": [1, 2], "key3": {"a": "b"}}') ;

      4. 筛选包含数据的SQL语句如下。

         SELECT  *
         FROM    json_table
         WHERE   j ? 'key1'
         ;

      5. 执行结果如下。

          id |                        j
         ----+-------------------------------------------------
           1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
           1 | {"key1": 1}
         (2 rows) 

    • 创建jsonb_holo_path_ops操作符号索引
      1. 创建表的SQL语句如下。

         DROP TABLE IF EXISTS json_table;
         CREATE TABLE IF NOT EXISTS json_table
         (
             id INT
             ,j jsonb
         );

      2. 创建jsonb_holo_path_ops操作符号索引的SQL语句如下。

         CREATE INDEX index_json on json_table USING GIN(j jsonb_holo_path_ops);

      3. 插入数据的SQL语句如下。

         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. 筛选包含'{"key1": "10"}'数据的SQL语句如下。

         SELECT  *
         FROM    json_table
         WHERE   j @> '{"key1": "10"}'::JSONB 
         ;

      5. 执行结果如下。

          id |                                   j                                    
         ----+------------------------------------------------------------------------
          10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
         (1 row)

      6. 使用explain命令查看执行计划如下。

         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

         执行计划中出现了Index Scan步骤，表明查询过程使用了索引。

JSONB列式存储优化

从Hologres V1.3版本开始，支持对于JSONB类型开启列存优化，能够减少JSONB的存储并加速查询。

如下图示例，开启列式存储后底层会自动将Schemaless的Jsonb列，转换为强Schema的列式存储，查询时就可以直接命中查询的列，提升查询性能。

• 语法解释。
  • 对某张表的某个列打开JSONB列存优化。

    -- 打开xx表的xx列的JSONB列式存储优化
    ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

    table_name为表名称；column_name为列名称。
    重要 打开JSONB列存优化后，系统在Compaction时将历史数据都转为列存，待Compaction完毕即完成历史数据的列存化。同时新写入的数据会按照列存存储。您可以使用vacuum <table_name>;命令强制触发Compaction操作，由于Compaction会消耗系统资源，建议在业务低峰期操作。待vacuum命令执行完毕，Compaction操作就执行完毕。
  • 关闭JSONB列存优化。

    -- 关闭xx表的xx列的JSONB列式存储优化
    ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

    table_name为表名称；column_name为列名称。
    重要 关闭JSONB列存优化后，系统在Compaction时将历史数据都转为标准的JSONB存储方式，待Compaction完毕即完成历史数据的转换。同时新写入的数据会按照标准的JSONB存储方式存储。您可以使用vacuum <table_name>;命令强制触发Compaction操作，由于Compaction会消耗系统资源，建议在业务低峰期操作。待vacuum命令执行完毕，Compaction操作就执行完毕。
  • 查看某张表的配置情况

    SELECT DISTINCT
        a.attnum as num,
        a.attname as name,
        format_type(a.atttypid, a.atttypmod) as type,
        a.attnotnull as notnull,
        com.description as comment,
        coalesce(i.indisprimary,false) as primary_key,
        def.adsrc as default,
        a.attoptions
    FROM pg_attribute a
    JOIN pg_class pgc ON pgc.oid = a.attrelid
    LEFT JOIN pg_index i ON
        (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
    LEFT JOIN pg_description com on
        (pgc.oid = com.objoid AND a.attnum = com.objsubid)
    LEFT JOIN pg_attrdef def ON
        (a.attrelid = def.adrelid AND a.attnum = def.adnum)
    WHERE a.attnum > 0 AND pgc.oid = a.attrelid
    AND pg_table_is_visible(pgc.oid)
    AND NOT a.attisdropped
    AND pgc.relname = '<table_name>'  -- 表名称
    ORDER BY a.attnum;

    table_name为表名称。

    返回结果可以看到某个列的attoptions属性为enable_columnar_type = ON，表示已经配置成功。

• 使用示例
  1. 创建表SQL命令如下。

     DROP TABLE IF EXISTS user_tags;
     
     -- 创建数据表
     BEGIN;
     CREATE TABLE IF NOT EXISTS user_tags (
         ds timestamptz,
         tags jsonb
     );
     COMMIT;

  2. 打开JSONB列存优化SQL命令如下。

     -- 打开tags列的JSONB列存优化
     ALTER TABLE user_tags ALTER COLUMN tags SET (enable_columnar_type = ON);

  3. 查看是否设置成功
     • 查看配置命令如下。

       SELECT DISTINCT
           a.attnum as num,
           a.attname as name,
           format_type(a.atttypid, a.atttypmod) as type,
           a.attnotnull as notnull,
           com.description as comment,
           coalesce(i.indisprimary,false) as primary_key,
           def.adsrc as default,
           a.attoptions
       FROM pg_attribute a
       JOIN pg_class pgc ON pgc.oid = a.attrelid
       LEFT JOIN pg_index i ON
           (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
       LEFT JOIN pg_description com on
           (pgc.oid = com.objoid AND a.attnum = com.objsubid)
       LEFT JOIN pg_attrdef def ON
           (a.attrelid = def.adrelid AND a.attnum = def.adnum)
       WHERE a.attnum > 0 AND pgc.oid = a.attrelid
       AND pg_table_is_visible(pgc.oid)
       AND NOT a.attisdropped
       AND pgc.relname = 'user_tags'  -- 表名
       ORDER BY a.attnum;

     • 结果
       可以看到tags行的attoptions属性是enable_columnar_type = on，表示已经配置成功。
  4. 导入数据SQL命令如下。

     INSERT INTO user_tags (ds, tags)
     SELECT
         '2022-01-01 00:00:00+08'
         , ('{"id":' || i || ',"first_name" :"Sig",  "gender" :"Male"}')::jsonb
     FROM
         generate_series(1, 1001) i;

  5. （可选）强制触发数据落盘。
     写入数据后，系统会在数据落盘时进行JSONB的列存优化，为了尽快看到效果，此处使用如下后台命令，强制触发数据落盘。

     SELECT hg_admin_command ('flush', 'table_name=user_tags');
     
     VACUUM user_tags;

  6. 样例查询
     使用如下SQL查询id为10的first_name。

     SELECT
         (tags -> 'first_name')::text AS first_name
     FROM
         user_tags
     WHERE (tags -> 'id')::int = 10;

  7. 查看列存优化是否生效
     • 通过如下命令查看执行计划。

       -- 显示详细的统计信息
       SET hg_experimental_show_execution_statistics_in_explain = ON;
       -- 查看执行计划
       EXPLAIN ANALYZE
       SELECT
           (tags -> 'first_name')::text AS first_name
       FROM
           user_tags
       WHERE (tags -> 'id')::int = 10;

     • 结果
       执行计划如下所示，其中结果中有columnar_access_used，表示JSONB列存优化生效。