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

  1. 云栖社区>
  2. 阿里云在线数据仓库服务 AnalyticDB PostgreSQL版 (原 HybridDB for PostgreSQL)>
  3. 博客>
  4. 正文

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

陆封 2019-11-18 14:40:48 浏览3041

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)

参考