postgres JSON

json和jsonb的比较：

注：因为读的多，所有没有特殊要求,推荐都用jsonb

1，json存储格式为文本而jsonb存储格式为二进制 ，因此json写入比jsonb快，但查询比jsonb慢。

2，jsonb输出的键的顺序和输入不一样，而json的输出是完全一样的

3，jsonb类型会去掉输入数据中键值的空格,而json不会

4，jsonb会删除重复的键，仅保留最后一个，而json不会

操作符

json&jsonb

操作符	类型	描述	实例	结果
->	int	数组元素（索引从 0 开始）	SELECT '[1,2,3]'::jsonb -> 1	2
->	text	键（key）	SELECT '{"name":"xiaomin"}'::jsonb ->'name'	"xiaomin"
->>	int	和->差不多，区别在以文本形式	SELECT '[1,2,3]'::jsonb -> 1	2
->>	text	->区别在以文本形式	SELECT '{"name":"xiaomin"}'::jsonb ->'name'	"xiaomin"
#>	text[]	解析逗号隔开	SELECT '{"info":{"name":"xioami"}}'::jsonb #>> '{info,name}'	"xiaomin"
#>>	text[]	和#>区别在于文本形式	SELECT '{"info":{"name":"xioami"}}'::jsonb #>> '{info,name}'	"xiaomin"

 

 

 

 

 

 

 

 

 

jsonb

操作符	类型	描述	实例	结果
||	jsonb	拼接（追加）	{"name": "xiaomin", "label": "hello"}	{"name": "xiaomin", "label": "hello"}
-	text	删除	SELECT '{"name":"xiaomin"}'::jsonb - 'name'	{}
......	https://edu.csdn.net/skill/pg/pg-69ad784e39c044d7a115cfd124e49ef1?category=607

 

 

 

 

 

 

 

函数

jsonb_object_keys（扁平化对象，对数组无用）

SELECT jsonb_object_keys('{"name":"xiao","age":18}'::jsonb)

 

json_extract_path和#>一样，不做多说

json_array_length（查询长度）

 SELECT json_array_length('[1,2,3,4]')

 json_array_elements（扁平化数组，对象无用）

SELECT json_array_elements('[{"name":"xiao"},{"name":"hone"},{"name":"mi"}]') #> '{name}' as test

 

也可以多个拼接 

SELECT json_array_elements('[{"name":"xiao","age":18},{"name":"hone","age":14},{"name":"mi","age":10}]') #> '{name}' as name,json_array_elements('[{"name":"xiao","age":18},{"name":"hone","age":14},{"name":"mi","age":10}]') #> '{age}' as age

 

 

实例 

建表test

注：关键看info其它的无关

插入一条数据

 

info:内容是

{
  "project": false,
  "project_edit": true,
  "project_query": false,
  "pm_query_statistics": true
}

基本查询

SELECT jsonb_object_keys(t.info::jsonb) as permission,id as permission_id from test as t

 

 

对应值查询，把key,value拿出来

-- 对应关系查询
select json_extract_path(t."info"::json,  jsonb_object_keys(t.info::jsonb)) as off,jsonb_object_keys(t.info::jsonb) as permission,id as permission_id from test t

添加

update test set info = info::jsonb || '{"query": true}'::jsonb where id = 1;

 

删除 

update test set info = info::jsonb::jsonb - 'pm_query_statistics' where id = 1;

删除多个，

update test set info = info::jsonb::jsonb - '键key' - '键key2' where id = 1;

 

更改 

UPDATE test set info = jsonb_set(info, '{query}'::_text, 'false'::jsonb, true)  where id = 1

新建视图，放到视图中

 注：视图中off字段json转bool会失败，强制转换即可，::text::bool

 SELECT (json_extract_path((t.info)::json, VARIADIC ARRAY[jsonb_object_keys(t.info)]))::text::bool AS off,
 jsonb_object_keys(t.info::jsonb)::VARCHAR as permission,
 id
   FROM test t