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数据类型

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

JSON Types

JSONB特性

存储差异对比

JSON

JSONB

JSONB支持索引

索引性能比较

JSON类型建立函数索引

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

存储对比

运算符差异

两者都支持的操作符

JSONB类型的操作符

其他差异

参考

云原生数据仓库 AnalyticDB PostgreSQL版

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像