在实际项目，PostgreSQL在关系型数据库的稳定性和性能方面均优与MySQL。但PostgreSQL也兼具NoSQL特性，且支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率。
json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。
图片发自简书App
注意：键值对的键必须使用双引号
查询JSONB中字段
SELECT FROM 表 WHERE INFO ->> “JSONB中字段名” ~ “.*要查询字段.*”
根据某一键值查找
SELECT FROM 表 WHERE info @> “{“age”:20}”
示例：
test=# SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
json
------------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
(1 row)
test=# SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
(1 row)
json（jsonb) 的常用函数及操作符
->
右操作符为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';
{"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的是否包含右侧的.
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
SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'ceeee', 'e'];
?column?
----------
t
(1 row)
Time: 0.315 ms
右操作符：text[]，所有这些元素是否存都在于json的顶级key中.
SELECT '["a", "b"]'::jsonb ?& array['a', 'b'];
?column?
----------
t
(1 row)
Time: 36.143 ms
SELECT '["a", "b"]'::jsonb ?& array['a', 'b', 'c'];
?column?
----------
f
(1 row)
Time: 0.370 ms
右操作符: jsonb, 拼接两个jsonb生成一个新的jsonb
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
右操作符：text，从左操作数中删除K/V或者字符串元素。
SELECT '{"a": "b"}'::jsonb - 'a';
?column?
----------
{}
(1 row)
Time: 0.357 ms
SELECT '["a", "b"]'::jsonb - 'a';
?column?
----------
["b"]
(1 row)
Time: 0.359 ms
右操作符：int， 删除指定索引的元素（负数表示从结尾开始）
SELECT '["a", "b"]'::jsonb - (-1);
?column?
----------
["a"]
(1 row)
Time: 0.319 ms
SELECT '["a", "b"]'::jsonb - 0;
?column?
----------
["b"]
(1 row)
Time: 0.319 ms
SELECT '["a", "b"]'::jsonb - 1;
?column?
----------
["a"]
(1 row)
Time: 0.305 ms
SELECT '["a", "b"]'::jsonb - 2;
?column?
------------
["a", "b"]
(1 row)
Time: 0.312 ms
右操作符： text[]， 删除字段或指定路径的元素.
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
?column?
-----------
["a", {}]
(1 row)
Time: 0.460 ms
SELECT '["a", {"b":1}]'::jsonb #- '{0}';
?column?
------------
[{"b": 1}]
(1 row)
Time: 0.329 ms
SELECT * from test_json ;
id | hello
----+---------
1 | hello
1 | hello2
2 | hello-2
2 | hello-3
(4 rows)
Time: 0.203 ms
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
SELECT * from article ;
id | content
----+-----------------------
1 | hello article content
(1 row)
Time: 0.199 ms
SELECT * from tags ;
aid | name
-----+------
1 | tag1
1 | tag2
(2 rows)
Time: 0.210 ms
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
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
json(jsonb)中的CRUD
添加jsonb的字段
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
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
删除jsonb的某字段
UPDATE test_json set hello = (hello - 'hello');
UPDATE 1
Time: 0.883 ms
SELECT * from test_json ;
hello
--------------------------
{"wolrd": "world-value"}
(1 row)
Time: 0.185 ms
