AnalyticDB for PostgreSQL 6.0新特性 JSONB数据类型

2021-07-02 06:05:02

AnalyticDB for PostgreSQL 6.0新特性 JSONB数据类型

JSON Types

JSON数据类型顾名思义是用来存储JSON数据的，这种数据也可以用text类型来存储，但是JSON数据类型会对数据做JSON规则校验，同时提供一些列的特定的JSON化的函数，让用户可以对这些数据作出一些特殊的操作。

JSONB特性

JSON数据格式有两种：json & jsonb,这两种类型在使用上几乎完全一致。

json数据类型直接存储输入文本的完全的拷贝。
jsonb数据类型以二进制格式进行存储

优势	缺点
更高效处理速度提升非常大(使用时不需要重新解析) 支持索引(GIN,BTree,Hash) 更简单的模式设计(替代EAV表模型)	导入时性能略有下降(额外的转换工作) 较纯文本可能占用更多的存储空间(较大的表占用空间) 某些查询可能会变慢(缺少统计信息，聚合操作会更慢)

存储差异对比

JSON	JSONB
直接存储输入文本的完全的拷贝	二进制格式进行存储
保存数据中语意无关的空格	不保存空格
保留JSON对象键的顺序	不保存对象键的顺序
保存重复键的对象，在查询的时候会将最后一个值当作有效值	不保存重复键的对象，如果有重复键输入的话，只有最后一个值会被保存下来

JSON

insert into jsontest values ('{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": false,
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}');

可以看到json数据类型保留了数据原格式的空格，保留了重复键'is_active'的两行记录，保留了对象键的顺序

select * from jsontest;
                           jdoc
-----------------------------------------------------------
 {                                                        +
     "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",      +
     "name": "Angela Barton",                             +
     "is_active": false,                                  +
     "is_active": true,                                   +
     "company": "Magnafone",                              +
     "address": "178 Howard Place, Gulf, Washington, 702",+
     "registered": "2009-11-07T08:53:22 +08:00",          +
     "latitude": 19.793713,                               +
     "longitude": 86.513373,                              +
     "tags": [                                            +
         "enim",                                          +
         "aliquip",                                       +
         "qui"                                            +
     ]                                                    +
 }
(1 row)

查询"is_active"键对应的值时，只显示最后一个值

select jdoc->'is_active' as is_active from jsontest;
 is_active
----------
 true
(1 row)

JSONB

insert into jsonbtest values ('{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": false,
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}');

可以看到jsonb数据类型去掉了所有的无效空格，未保存键的顺序(键"tags"的顺序与插入时不一致了，现在在第三个，插入时是最后一个)。重复键"is_active"只保留了最后一个值。

select * from jsonbtest;
                                                                                                                                                   jdoc
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
 {"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", "name": "Angela Barton", "tags": ["enim", "aliquip", "qui"], "address": "178 Howard Place, Gulf, Washington, 702", "company": "Magnafone", "latitude": 19.
793713, "is_active": true, "longitude": 86.513373, "registered": "2009-11-07T08:53:22 +08:00"}
(1 row)

因此，在大部分场景下，应该使用jsonb类型来存储JSON数据，除非有非常特殊的需求，比如需要保留原来数据的顺序。

JSONB支持索引

JSON	JSONB
CREATE INDEX jsonidx ON jsontest USING gin (jdoc); ERROR: data type json has no default operator class for access method "gin" HINT: You must specify an operator class or define a default operator class for the data type.	CREATE INDEX jsonbidx ON jsonbtest USING gin (jdoc); CREATE INDEX

ps: 在JSON类型的列上无法直接建索引，但可以在JSON类型的列上建函数索引

CREATE INDEX ON jsontest USING btree (json_extract_path_text(jdoc,'name'));
CREATE INDEX

通常情况下，在JSONB类型上都会考虑建GIN索引，而不是Btree索引。因为Btree索引可能效率不高，原因是Btree索引不关心JSONB内部的数据结构，只是简单的按照比较整个JSONB大小的方式进行索引，其比较规则如下:
Object > Array > Boolean > Number > String > NULL
n个k/v对的Object > n-1个k/v对的Object
n个元素的Array > n-1个元素的Array
键值之间的比较是按存储顺序进行的
数组是按元素的顺序进行比较的
在JSONB上创建GIN索引的方式有两种：
使用默认的jsonb_ops操作符创建
使用jsonb_path_ops操作符创建
GIN默认的操作符创建索引语法如下:
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_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项的，而jsonb_path_ops则只为每个value创建一个索引项。例如：有一个项"{"foo":{"bar":"baz"}}",对于jsonb_path_ops是把foo、bar和baz组合成一个hash值作为索引项的，而jsonb_ops则会分别为每个值创建一个索引项，一共创建三个。因为少了很多索引项，所以通常jsonb_path_ops的索引要比jsonb_ops的小很多，这样当前也就会带来性能上的提升。

索引性能比较

JSON类型建立函数索引

CREATE TABLE jtest1 (
    id int,
    jdoc json
);

CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
    ARRAY (
        SELECT substring(
            '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
            FROM (ceil(random()*62))::int FOR 1
        )
        FROM generate_series(1, $1)
    ),
    ''
)
$BODY$
LANGUAGE sql VOLATILE;

insert into jtest1 select t.seq, ('{"a":{"a1":"a1a1", "a2":"a2a2"}, 
"name":"'||random_string(10)||'","b":"bbbbb"}')::json from
generate_series(1, 10000000) as t(seq);

# 建立函数索引
CREATE INDEX ON jtest1 USING btree (json_extract_path_text(jdoc,'name'));
# analyze
ANALYZE jtest1;

未走索引查询

EXPLAIN ANALYZE SELECT * FROM jtest1 where jdoc->>'name' = 'N9WP5txmVu';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1807.00 rows=100 width=71) (actual time=5361.924..5860.827 rows=1 loops=1)
   ->  Seq Scan on jtest1  (cost=0.00..1807.00 rows=50 width=71) (actual time=0.058..5361.406 rows=1 loops=1)
         Filter: ((jdoc ->> 'name'::text) = 'N9WP5txmVu'::text)
 Planning time: 0.132 ms
   (slice0)    Executor memory: 59K bytes.
   (slice1)    Executor memory: 91K bytes avg x 2 workers, 91K bytes max (seg0).
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 5861.425 ms
(9 rows)

走函数索引

EXPLAIN ANALYZE SELECT * FROM jtest1 where json_extract_path_text(jdoc,'name') = 'N9WP5txmVu';
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.19..200.20 rows=1 width=71) (actual time=1.458..1.532 rows=1 loops=1)
   ->  Index Scan using jtest1_json_extract_path_text_idx on jtest1  (cost=0.19..200.20 rows=1 width=71) (actual time=0.152..0.153 rows=1 loops=1)
         Index Cond: (json_extract_path_text(jdoc, VARIADIC '{name}'::text[]) = 'N9WP5txmVu'::text)
 Planning time: 0.205 ms
   (slice0)    Executor memory: 92K bytes.
   (slice1)    Executor memory: 60K bytes avg x 2 workers, 60K bytes max (seg0).
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 18.943 ms
(9 rows)

JSONB类型建立函数索引性能对比

CREATE TABLE jtest2 (
    id int,
    jdoc jsonb
);

CREATE TABLE jtest3 (
    id int,
    jdoc jsonb
);

insert into jtest2 select id, jdoc::jsonb from jtest1;
insert into jtest3 select id, jdoc::jsonb from jtest1;

CREATE INDEX idx_jtest2 ON jtest2 USING gin(jdoc);
CREATE INDEX idx_jtest3 ON jtest3 USING gin(jdoc jsonb_path_ops);

ANALYZE jtest2;
ANALYZE jtest3;

未建索引

EXPLAIN ANALYZE SELECT * FROM jtest2 where jdoc @> '{"name":"N9WP5txmVu"}';
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..162065.73 rows=10100 width=88) (actual time=1343.248..1777.605 rows=1 loops=1)
   ->  Seq Scan on jtest2  (cost=0.00..162065.73 rows=5050 width=88) (actual time=0.042..1342.426 rows=1 loops=1)
         Filter: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
 Planning time: 0.172 ms
   (slice0)    Executor memory: 59K bytes.
   (slice1)    Executor memory: 91K bytes avg x 2 workers, 91K bytes max (seg0).
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 1778.234 ms
(9 rows)

使用jsonb_ops操作符创建索引

EXPLAIN ANALYZE SELECT * FROM jtest2 where jdoc @> '{"name":"N9WP5txmVu"}';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=88.27..13517.81 rows=10100 width=88) (actual time=0.655..0.659 rows=1 loops=1)
   ->  Bitmap Heap Scan on jtest2  (cost=88.27..13517.81 rows=5050 width=88) (actual time=0.171..0.172 rows=1 loops=1)
         Recheck Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
         ->  Bitmap Index Scan on idx_jtest2  (cost=0.00..85.75 rows=5050 width=0) (actual time=0.217..0.217 rows=1 loops=1)
               Index Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
 Planning time: 0.151 ms
   (slice0)    Executor memory: 69K bytes.
   (slice1)    Executor memory: 628K bytes avg x 2 workers, 632K bytes max (seg1).  Work_mem: 9K bytes max.
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 1.266 ms
(11 rows)

使用jsonb_path_ops操作符创建索引

EXPLAIN ANALYZE SELECT * FROM jtest3 where jdoc @> '{"name":"N9WP5txmVu"}';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=84.28..13513.81 rows=10101 width=88) (actual time=0.710..0.711 rows=1 loops=1)
   ->  Bitmap Heap Scan on jtest3  (cost=84.28..13513.81 rows=5051 width=88) (actual time=0.179..0.181 rows=1 loops=1)
         Recheck Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
         ->  Bitmap Index Scan on idx_jtest3  (cost=0.00..81.75 rows=5051 width=0) (actual time=0.106..0.106 rows=1 loops=1)
               Index Cond: (jdoc @> '{"name": "N9WP5txmVu"}'::jsonb)
 Planning time: 0.144 ms
   (slice0)    Executor memory: 69K bytes.
   (slice1)    Executor memory: 305K bytes avg x 2 workers, 309K bytes max (seg1).  Work_mem: 9K bytes max.
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 1.291 ms
(11 rows)

索引大小对比

select pg_indexes_size('jtest2');
 pg_indexes_size
-----------------
       565018624
(1 row)

select pg_indexes_size('jtest3');
 pg_indexes_size
-----------------
       473202688
(1 row)

可以看到使用jsonb_ops操作符创建索引比使用jsonb_path_ops操作符创建索引性能好一些，但是索引占的空间更大一些。

存储对比

postgres=# select pg_size_pretty(pg_relation_size('jtest1'));
 pg_size_pretty
----------------
 965 MB
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('jtest2'));
 pg_size_pretty
----------------
 1119 MB
(1 row)

可以看到json类型的表比jsonb类型表的数据量要小一些

运算符差异

两者都支持的操作符

操作符	右操作数类型	描述	例子	结果
->	int	取JSON数组的元素(下标从0开始)	'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2	{"c":"baz"}
->	text	通过key取JSON中的子对象	'{"a": {"b":"foo"}}'::json->'a'	{"b":"foo"}
->>	int	取JSON数组的元素，返回的是一个text类型	'[1,2,3]'::json->>2	3
->>	text	通过key取JSON中的子对象，返回的是一个text类型	'{"a":1,"b":2}'::json->>'b'	2
#>	text[]	通过指定路径取JSON中的对象	'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'	{"c": "foo"}
#>>	text[]	通过指定路径取JSON中的对象，返回的是一个text类型	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'	3

注意: JSONB支持number,boolean类型的-> 操作过滤，而JSON不支持。

JSON	JSONB
select count() from jsontest where jdoc->'is_active' = 'true'; ERROR: operator does not exist: json = unknown LINE 1: ...ect count() from jsontest where jdoc->'is_active' = 'true'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.	select count(*) from jsonbtest where jdoc->'is_active' = 'true'; count ------- 2 (1 row)

而->>操作符，两种类型都支持过滤，且支持string类型。

JSON	JSONB
select count(*) from jsontest where jdoc->>'company' = 'Magnafone'; count ------- 2 (1 row)	select count(*) from jsonbtest where jdoc->>'company' = 'Magnafone'; count ------- 2 (1 row)

JSONB类型的操作符

操作符	右操作数据类型	描述	例子
=	jsonb	两个JSON对象的内容是否相等	'[1,2]'::jsonb = '[1,2]'::jsonb
@>	jsonb	左边的JSON对象是否包含右边的JSON对象	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@	jsonb	左边的JSON对象是否包含于右边的JSON对象	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?	text	指定的字符串是否存在与JSON对象中的key或者字符串类型的元素中	'{"a":1, "b":2}'::jsonb ? 'b'
?\|	text[]	右值字符串数组是否存在任一元素在JSON对象字符串类型的key或者元素中	'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c']
?&	text[]	右值字符串数组是否所有元素在JSON对象字符串类型的key或者元素中	'["a", "b"]'::jsonb ?& array['a', 'b']

以上操作符都是JSONB类型支持而JSON不支持的

JSON	JSONB
select '[1,2]'::json = '[1,2]'::json as check; ERROR: operator does not exist: json = json LINE 1: select '[1,2]'::json = '[1,2]'::json as check; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.	select '[1,2]'::jsonb = '[1,2]'::jsonb as check; check ------- t (1 row)

其他差异

一个语意无关的细节值得注意，jsonb数据类型输出数字类型的方式不一样，

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

参考

https://www.postgresql.org/docs/9.4/datatype-json.html
PostgreSQL修炼之道从小工到专家

码农公寓

AnalyticDB for PostgreSQL 6.0新特性 JSONB数据类型

JSON Types

JSONB特性

存储差异对比

JSON

JSONB

JSONB支持索引

索引性能比较

JSON类型建立函数索引

JSONB类型建立函数索引性能对比

存储对比

运算符差异

两者都支持的操作符

JSONB类型的操作符

其他差异

参考

相关文章