AnalyticDB for PostgreSQL 6.0新特性 JSONB資料類型

JSON Types

JSON資料類型顧名思義是用來存儲JSON資料的，這種資料也可以用text類型來存儲，但是JSON資料類型會對資料做JSON規則校驗，同時提供一些列的特定的JSON化的函數，讓使用者可以對這些資料作出一些特殊的操作。

JSONB特性

JSON資料格式有兩種：json & jsonb,這兩種類型在使用上幾乎完全一緻。

json資料類型直接存儲輸入文本的完全的拷貝。
jsonb資料類型以二進制格式進行存儲

優勢	缺點
更高效處理速度提升非常大(使用時不需要重新解析) 支援索引(GIN,BTree,Hash) 更簡單的模式設計(替代EAV表模型)	導入時性能略有下降(額外的轉換工作) 較純文字可能占用更多的存儲空間(較大的表占用空間) 某些查詢可能會變慢(缺少統計資訊，聚合操作會更慢)

優勢

缺點

更高效

處理速度提升非常大(使用時不需要重新解析)

支援索引(GIN,BTree,Hash)

更簡單的模式設計(替代EAV表模型)

導入時性能略有下降(額外的轉換工作)

較純文字可能占用更多的存儲空間(較大的表占用空間)

某些查詢可能會變慢(缺少統計資訊，聚合操作會更慢)

存儲差異對比

JSON	JSONB
直接存儲輸入文本的完全的拷貝	二進制格式進行存儲
儲存資料中語意無關的空格	不儲存空格
保留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)

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支援索引


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

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"}
->>		取JSON數組的元素，傳回的是一個text類型	'[1,2,3]'::json->>2	3
		通過key取JSON中的子對象，傳回的是一個text類型	'{"a":1,"b":2}'::json->>'b'	2
#>	text[]	通過指定路徑取JSON中的對象	'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'	{"c": "foo"}
#>>		通過指定路徑取JSON中的對象，傳回的是一個text類型	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

注意: JSONB支援number,boolean類型的

->

操作過濾，而JSON不支援。


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)

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

-------

(1 row)

而

->>

操作符，兩種類型都支援過濾，且支援string類型。


select count(*) from jsontest where jdoc->>'company' = 'Magnafone';	select count(*) from jsonbtest where jdoc->>'company' = 'Magnafone';

JSONB類型的操作符

	右操作資料類型
=	jsonb	兩個JSON對象的内容是否相等	'[1,2]'::jsonb = '[1,2]'::jsonb
@>		左邊的JSON對象是否包含右邊的JSON對象	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@		左邊的JSON對象是否包含于右邊的JSON對象	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?		指定的字元串是否存在與JSON對象中的key或者字元串類型的元素中	'{"a":1, "b":2}'::jsonb ? 'b'
?\|		右值字元串數組是否存在任一進制素在JSON對象字元串類型的key或者元素中	'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c']
?&		右值字元串數組是否所有元素在JSON對象字元串類型的key或者元素中	'["a", "b"]'::jsonb ?& array['a', 'b']

以上操作符都是JSONB類型支援而JSON不支援的


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; ^	select '[1,2]'::jsonb = '[1,2]'::jsonb as check; check t

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;

select '[1,2]'::jsonb = '[1,2]'::jsonb as check;

check

其他差異

一個語意無關的細節值得注意，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資料類型

AnalyticDB for PostgreSQL 6.0新特性 JSONB資料類型

JSON Types

JSONB特性

存儲差異對比

JSONB支援索引

索引性能比較

JSON類型建立函數索引

JSONB類型建立函數索引性能對比

存儲對比

運算符差異

兩者都支援的操作符

JSONB類型的操作符

其他差異

參考

繼續閱讀

SpringMVC 傳回json的兩種方式

json傳輸資料解決中文亂碼問題

壓縮編碼M-JPEG、MPEG4、H.264

轉詳解C#資料庫存取圖檔三大方式

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

關于 underscore 中模闆引擎的應用示範樣例

underscore 模闆标簽修改。

Ajax——模闆引擎

使用underscore的template自定義模闆

underscore模闆功能的使用和學習

[HTML5]自定義屬性 data-* 和 jQuery.data 詳解

七牛雲-C#SDK-上傳-前期準備

vue-cli簡介（中文翻譯）

Ajax發送和擷取json資料到Spring mvc 1.spring mvc後端2.web前段

JSONObject包導入異常 java.lang.NoClassDefFoundErrorweb項目的導入包的問題