PostgreSQL中的json与jsonb

hstore vs json vs jsonb

首先，hstore是一个扩展模块，它允许你保存key=>values键值对，且键值都只能是texts类型（但是，值也允许sql的NULL）

json与jsonb 允许你保存一个有效的json值(定义).

例如，以下都是有效的json表示方式: null, true, [1, false, "string", {"foo":"bar"}], {"foo":"bar", "baz":[null]}.

相比json, hstore只是它的一个很小的子集(但是，如果你只需要这个子集，也OK的.)

json与jsonb的区别主要是它们的存储方式：

json是保存为文本格式的
jsonb是保存为二进制格式的

这主要有三方面的影响：

jsonb通常比json占用更多的磁盘空间（有些情况不是）
jsonb比json的写入更耗时间
json的操作比jsonb的操作明显更耗时间（在操作一个json类型值时需要每次都去解析）

当jsonb将在未来稳定版发行可用时，这有两个主要使用情况，你很容易在他们之间选择的：

如果你的应用只用json表示，PostgreSQL只用于保存与获取时，你应该使用json.
如果你需要在PostgreSQL中做比较多的json值的操作，或者在一些json字段上使用索引时，你应该使用jsonb

官方文档上说:

有两个JSON数据类型：json和jsonb。它们接受几乎相同的值组作为输入。它们实际的主要差别是效率。json 数据类型存储输入文本的精确拷贝，处理函数必须在每个执行上重新解析；而jsonb数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析。jsonb也支持索引，这也是一个明显的优势。

因为json类型存储输入文本的精确拷贝，它将保存令牌间语义上无关紧要的空格，和JSON对象中键的顺序。另外，如果值中的一个JSON对象多次包含相同的键，那么保存所有的键/值对。（处理函数将最后一个值当做操作值。）相比之下， jsonb不保存空格，也不保存对象键的顺序，并且不保存重复对象键。如果在输入中指定了重复的键，那么只保存最后一个值。

json（jsonb) 的常用函数及操作符

functions-json

`->`

右操作符为int：获取JSON数组元素（索引从0开始）

select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;
  ?column?   
-------------
 {"c":"baz"}
(1 row)

Time: 1.240 ms

右操作符为text：通过键获取json值.

SELECT  '{"a": {"b":"foo"}}'::json->'a';
  ?column?   
-------------
 {"b":"foo"}
(1 row)

Time: 0.685 ms

`->>`

右操作符为int：获取JSON数组元素为text

SELECT  '[1,2,3]'::json->>2;
 ?column? 
----------
 3
(1 row)

Time: 0.530 ms

右操作符为text：通过键获取json值为text

 SELECT  '{"a":1,"b":2}'::json->>'b';
 ?column? 
----------
 2
(1 row)

Time: 0.585 ms

`#>`

右操作符为: text[], 在指定的路径获取JSON对象。

SELECT  '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';
   ?column?   
--------------
 {"c": "foo"}
(1 row)

Time: 0.665 ms

即在获取a.b的值

`#>>`

右操作符为: text[], 在指定的路径获取JSON对象为text

SELECT  '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
 ?column? 
----------
 3
(1 row)

Time: 0.556 ms

即获取a[2]的值并转为text.

jsonb 操作符

`@>`

右操作数的类型： jsonb, 左侧的JSONB的是否包含右侧的.

SELECT  '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
 ?column? 
----------
 t
(1 row)

Time: 0.599 ms

`<@`

右操作数的类型： jsonb, 右侧的JSONB的是否包含左侧的.

SELECT  '{"a":1, "b":2}'::jsonb <@ '{"b":2}'::jsonb;
 ?column? 
----------
 f
(1 row)

Time: 0.435 ms

`?`

右操作符: text, 该字符串是否存在于json的顶级key中.

SELECT '{"a":1, "b":2}'::jsonb ? 'b';
 ?column? 
----------
 t
(1 row)

Time: 0.551 ms

`?|`

右操作符：text[]，这些元素之一是否存在于json的顶级key中.

SELECT  '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'ceeee', 'e'];
 ?column? 
----------
 t
(1 row)

Time: 0.315 ms

`？&`

右操作符：text[]，所有这些元素是否存都在于json的顶级key中.

[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb ?& array['a', 'b'];
 ?column? 
----------
 t
(1 row)

Time: 36.143 ms
[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb ?& array['a', 'b', 'c'];
 ?column? 
----------
 f
(1 row)

Time: 0.370 ms

`||`

右操作符: jsonb, 拼接两个jsonb生成一个新的jsonb

[local]:5432 sky@sky=# SELECT  '["a", "b", {"hello":"world"}]'::jsonb || '["c", "d", {"hello":"world"}]'::jsonb;
                           ?column?                           
--------------------------------------------------------------
 ["a", "b", {"hello": "world"}, "c", "d", {"hello": "world"}]
(1 row)

Time: 0.359 ms
[local]:5432 sky@sky=#

`-`

右操作符：text，从左操作数中删除K/V或者字符串元素。

[local]:5432 sky@sky=# SELECT  '{"a": "b"}'::jsonb - 'a';
 ?column? 
----------
 {}
(1 row)

Time: 0.357 ms
[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb - 'a';
 ?column? 
----------
 ["b"]
(1 row)

Time: 0.359 ms

右操作符：int，删除指定索引的元素（负数表示从结尾开始）

[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb - (-1);
 ?column? 
----------
 ["a"]
(1 row)

Time: 0.319 ms
[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb - 0;
 ?column? 
----------
 ["b"]
(1 row)

Time: 0.319 ms
[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb - 1;
 ?column? 
----------
 ["a"]
(1 row)

Time: 0.305 ms
[local]:5432 sky@sky=# SELECT  '["a", "b"]'::jsonb - 2;
  ?column?  
------------
 ["a", "b"]
(1 row)

Time: 0.312 ms
[local]:5432 sky@sky=#

`#-`

右操作符： text[]，删除字段或指定路径的元素.

[local]:5432 sky@sky=# SELECT  '["a", {"b":1}]'::jsonb #- '{1,b}';
 ?column?  
-----------
 ["a", {}]
(1 row)

Time: 0.460 ms
[local]:5432 sky@sky=# SELECT  '["a", {"b":1}]'::jsonb #- '{0}';
  ?column?  
------------
 [{"b": 1}]
(1 row)

Time: 0.329 ms

常用json函数

row_to_json()

localhost:5433 sky@sky=# SELECT * from test_json ;
 id |  hello  
----+---------
  1 | hello
  1 | hello2
  2 | hello-2
  2 | hello-3
(4 rows)

Time: 0.203 ms
localhost:5433 sky@sky=# SELECT row_to_json(test_json) from test_json ;
        row_to_json         
----------------------------
 {"id":1,"hello":"hello"}
 {"id":1,"hello":"hello2"}
 {"id":2,"hello":"hello-2"}
 {"id":2,"hello":"hello-3"}
(4 rows)

Time: 0.229 ms

localhost:5433 sky@sky=# SELECT * from article ;
 id |        content        
----+-----------------------
  1 | hello article content
(1 row)

Time: 0.199 ms
localhost:5433 sky@sky=# SELECT * from tags ;
 aid | name 
-----+------
   1 | tag1
   1 | tag2
(2 rows)

Time: 0.210 ms
localhost:5433 sky@sky=# select row_to_json(t)
from (
  select id,
    (
      select array_to_json(array_agg(row_to_json(d)))
      from (
        select name
        from tags
        where tags.aid = article.id
      ) d
    ) as tags
  from article
  where id = 1
) t;
                    row_to_json                    
---------------------------------------------------
 {"id":1,"tags":[{"name":"tag1"},{"name":"tag2"}]}
(1 row)

Time: 0.349 ms
localhost:5433 sky@sky=#

localhost:5433 sky@sky=# select row_to_json(t) from ( select *, ( SELECT array_to_json(array_agg(name))  as name  from tags where aid = article.id) as tags from article ) as t ;
                            row_to_json                            
-------------------------------------------------------------------
 {"id":1,"content":"hello article content","tags":["tag1","tag2"]}
(1 row)

Time: 0.304 ms
localhost:5433 sky@sky=#

json(jsonb)中的CRUD

添加jsonb的字段

localhost:5433 sky@sky=# create TABLE test_json(hello jsonb);
CREATE TABLE
Time: 5.642 ms

localhost:5433 sky@sky=# INSERT INTO test_json VALUES ('{"hello":"hello-value", "wolrd":"world-value"}');
INSERT 0 1
Time: 1.722 ms
localhost:5433 sky@sky=# SELECT * from test_json ;
                      hello                       
--------------------------------------------------
 {"hello": "hello-value", "wolrd": "world-value"}
(1 row)

Time: 0.179 ms
localhost:5433 sky@sky=# UPDATE test_json set hello = jsonb_set(hello, '{hello}', '"hello-new-value"'::text::jsonb, true);
UPDATE 1
Time: 0.994 ms
localhost:5433 sky@sky=# SELECT * from test_json ;
                        hello                         
------------------------------------------------------
 {"hello": "hello-new-value", "wolrd": "world-value"}
(1 row)

Time: 0.174 ms
localhost:5433 sky@sky=#

删除jsonb的某字段

localhost:5433 sky@sky=# UPDATE test_json set hello = (hello - 'hello');
UPDATE 1
Time: 0.883 ms
localhost:5433 sky@sky=# SELECT * from test_json ;
          hello           
--------------------------
 {"wolrd": "world-value"}
(1 row)

Time: 0.185 ms
localhost:5433 sky@sky=#

Contents