JSON和JSONB类型

JSON和JSONB介绍

使用限制

JSON和JSONB操作符

常用JSON和JSONB操作符

额外的JSON和JSONB操作符

JSON和JSONB函数

JSON和JSONB处理函数

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

解析函数

JSONB索引

GIN索引可以通过原生PostgreSQL操作符和Hologres操作符实现，使用示例如下。

原生PostgreSQL操作符

• 创建jsonb_ops操作符索引。

  --1、创建表
  BEGIN;
  DROP TABLE IF EXISTS json_table;
  
  CREATE TABLE IF NOT EXISTS json_table
  (
      id INT
      ,j jsonb
  );
  COMMIT;
  
  --2、创建jsonb_ops操作符索引
  CREATE INDEX index_json on json_table USING GIN(j);
  
  --3、插入数据
  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、查询数据
  SELECT  * FROM    json_table WHERE   j ? 'key1';
  --返回结果
   id |                        j
  ----+-------------------------------------------------
    1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
    1 | {"key1": 1}
  
                      

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

  explain SELECT  * FROM    json_table WHERE   j ? 'key1';
  
  QUERY PLAN
  Gather  (cost=0.00..0.26 rows=1000 width=12)
    ->  Local Gather  (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'::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.3.0

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

  --1、创建表
  BEGIN;
  DROP TABLE IF EXISTS json_table;
  
  CREATE TABLE IF NOT EXISTS json_table
  (
      id INT
      ,j jsonb
  );
  COMMIT;
  
  --2、创建jsonb_ops操作符索引
  CREATE INDEX index_json on json_table USING GIN(j jsonb_path_ops);
  
  --3、插入数据
  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"}'的数据
  SELECT  * FROM    json_table WHERE   j @> '{"key1": "10"}'::JSONB;
  --返回结果
   id |                                   j
  ----+------------------------------------------------------------------------
   10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
  (1 row)
                      

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

  explain SELECT  * FROM    json_table WHERE   j @> '{"key1": "10"}'::JSONB;
  
                                          QUERY PLAN
  -------------------------------------------------------------------------------------------
   Gather  (cost=0.00..0.26 rows=1000 width=12)
     ->  Local Gather  (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.3.0
  (8 rows)
                      

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

Hologres操作符

高级调优：列式JSONB存储

GIN索引只是计算层的优化，实际计算时还需要扫描整个JSON内容。为了提升JSONB数据的查询效率，Hologres从 V1.3版本开始支持JSONB数据的列式存储，通过存储层的优化，使得JSONB数据能够像结构化数据一样按列式存储，有效提高数据的压缩效率，同时也提升JSONB数据的查询效率。

JSONB列式存储详情请参见列式JSONB。