PostgreSQL之JSONB常用操作及应用

PostgreSQL之JSONB常用操作及应用

Postgresql引入JSONB之后，很好的整合了关系型数据和非关系型数据，使得我们在应用的时候大大的提升了性能效率。本文以Postgresql 9.6为示例，分两部分简要介绍一些常用操作符和函数的使用。

1、基础部分

1.1 常用操作符

※json和jsonb通用操作符

操作符	操作类型	描述
->	INT	获取JSON数组元素（从零开始索引，从末尾开始计算负整数）
->	TEXT	按键获取JSON对象字段
->>	INT	获取JSON数组元素作为文本
->>	TEXT	按键获取JSON对象字段作为文本
#>	TEXT[]	在指定路径获取JSON对象
#>>	TEXT[]	在指定路径获取JSON对象作为文本

     

※jsonb拓展操作符

操作符	操作类型	描述
@>	JSONB	左边的JSON值顶层是否包含右边JSON路径/值项
?	TEXT	字符串是否作为顶层键值存在于JSON值中
?|	TEXT[]	数组内的字符串是否存在顶层键值
?&	TEXT[]	数组内的字符串是否都是顶层键值
||	JSONB	将两个JSON对象连接成一个新的对象
-	INT	删除指定索引位置的数组元素（若顶层容器不是数组，会报错）
-	TEXT	根据键值匹配删除对应的键值对
#-	TEXT[]	删除指定路径的字段或元素

 

1.2操作符使用示例

1) -> 根据索引或键获取

 select '[{"A":1},{"B":1}]'::json -> 0; 输出{"A":1}

select '[{"A":1},{"B":1}]'::json -> -1; 输出{"B":1}

select '{"A":1,"B":{"a":1,"b":2}}'::json -> 'B'; 输出{"a":1,"b":2}

2）#> 根据路径获取

select '{"a":[1,2,3],"b":[4,5,6]}'::json #> '{a,2}'; 输出3

select '[{"A":1,"B":{"b":{"i":2}}},{"A":3,"B":4}]'::jsonb #>'{0,B,b}'; 输出{"i": 2}

3) @> 判断是否包含

select '[{"A":1,"B":2},{"A":3,"B":4}]'::jsonb @> '[{"A":1}]'::jsonb; 输出t

select '{"A":1,"B":{"a":1,"b":2}}'::jsonb  @> '{"A":1}'::jsonb; 输出t

4) ? 判断顶层键值是否存在

                    select '{"A":1,"B":{"a":1,"b":2}}'::jsonb ? 'B'; 输出t

                    select '{"A":1,"B":{"a":1,"b":2}}'::jsonb ? 'a'; 输出f

                   5) ?| 判断多个顶层键值是否存在

                    select '{"A":1,"B":{"a":1,"b":2}}'::jsonb ?| '{B,a}'; 输出t

         6) ?& 判断多个顶层键值是否存在

                    select '{"A":1,"B":{"a":1,"b":2}}'::jsonb ?& '{A,B}'; 输出t

                    select '{"A":1,"B":{"a":1,"b":2}}'::jsonb ?& '{A,B,a}'; 输出f

                   7) || 拼接两个JSONB

               select '[{"A":1}]'::jsonb || '[{"A":2}]'::jsonb; 输出[{"A": 1}, {"A": 2}]

                    select '{"A":1}'::jsonb || '{"B":2}'::jsonb ; 输出{"A": 1, "B": 2}

              8）- 根据索引或者键删除

                    select '[{"A":1},{"B":2}]'::jsonb - 0; 输出[{"B": 2}]

                    select '{"A":1,"B":{"a":1,"b":2}}'::jsonb - 'A'; 输出{"B": {"a": 1, "b": 2}}

9）#- 根据路径删除

select '{"A":1,"B":{"a":1,"b":2}}'::jsonb #- '{"B","a"}'; 输出{"A": 1, "B": {"b": 2}}    

select '[{"A":1,"B":2},{"A":3,"B":4}]'::jsonb #- '{1,A}'; 输出[{"A": 1, "B": 2}, {"B": 4}]

 

1.3常用JSONB函数示例

                  1)  jsonb_object_keys(jsonb)    

--返回最外层JSON对象中的键集

            select jsonb_object_keys('{"A":1,"B":{"a":1,"b":2}}'::jsonb);

输出：

json_object_keys

------------------

 A

 B

2）jsonb_array_elements(jsonb)

   --将JSON数组扩展为一组JSON值

   select jsonb_array_elements('[{"A":1,"B":2},{"A":3,"B":4}]'::jsonb);

输出：

jsonb_array_elements

------------------

 {"A": 1, "B": 2}

 {"A": 3, "B": 4}

          3）jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

--将目标jsonb对应路径下的字段设置新值，create_missing为true说明这个字段若存在则更新，不存在则新建 

select  jsonb_set('{"A":1,"B":{"a":1,"b":2}}'::jsonb,'{"B","c"}'::text[],'0'::jsonb,true);

输出：

 jsonb_set

------------------

{"A": 1, "B": {"a": 1, "b": 2, "c": 0}}

     4）jsonb_each(jsonb)

--将最外层的JSON对象扩展为一组键/值对

select * from  jsonb_each(' {"1057": {"num": 1057, "name": "1057号"}, "1058": {"num": 1058, "name": "1058号"}}');

输出：

key | value

----- + -------

1057  | {"num": 1057, "name": "1057号"}

 1058  | {"num": 1058, "name": "1058号"}

 

     5) jsonb_to_record(jsonb)

--将单个JSON对象以指定的记录结构展开

select * from jsonb_to_record('{"num": 1057, "name": "1057号"}'::jsonb)as x( num int, name text);

输出：

num | name

----- + -------

1057  | 1057号

          6) jsonb_to_recordset(jsonb)

                      --将单个JSON对象数组以指定的记录结构展开

select * from jsonb_to_recordset('[{"num": 1057, "name": "1057号"},{"num": 1058, "name": "1058号"}]'::jsonb)as x( num int, name text);

输出：

num | name

----- + -------

1057  | 1057号

1058  | 1058号

   这里暂且只列出常用函数，更多操作请参考官方文档：https://www.postgresql.org/docs/9.6/functions-json.html

2、实际应用

    下面以两个实际应用中常使用到的json数据格式（JsonArray、JsonObject），示例一下PG是如何使用JSONB的常用操作符和函数来完成一些业务场景应用。

2.1准备表结构和数据

第一种jsonb数据格式为对象数组

CREATE TABLE person(id serial, info jsonb);

第二种jsonb数据格式为key/value键值对

CREATE TABLE person2(id serial, info jsonb);

 

2.2查询

       --1) 查询 person表中num为19和person2表中key为19所在的行数据：

SELECT * FROM person WHERE info @> '[{"num":19}]'::jsonb;

SELECT * FROM person2 WHERE info ? '19';

 

--2) 查询id为5，num为23的json对象：

第一种SELECT p.id, arr.value FROM person p, jsonb_array_elements(info) arr WHERE p.id=5 AND  arr.value->>'num' ='23';

第二种SELECT p.id, map.value FROM person2 p, jsonb_each(info) map WHERE  p.id=5 AND  map.value ->>'num' = '23';

--3）查询person表， id为1，num为2的对象在数组中的索引位置：

     SELECT ORDINALITY::INT - 1 as ord FROM person p, jsonb_array_elements(info) WITH ORDINALITY WHERE value->>'num' = '2' and p.id=1 ;

--4) 将id为3，num大于12的json对象展开成列表形式：

第一种SELECT p.id , x.* FROM person p, jsonb_to_recordset(info) as x( num int, name text) WHERE p.id =3 and x.num >12;

第二种 SELECT p.id ,map.value->> 'num' as num  ,map.value->> 'name' as name  FROM person2 p,jsonb_each(info) map

WHERE p.id = 3 AND (map.value->>'num')::INT > 12 ;

 

 

2.3修改

--1）在id=1的info对象中新增一条数据：

        UPDATE person SET info = info || '[{"num":0,"name":"0号"}]'::jsonb WHERE id=1;

UPDATE person2 SET info = info || '{"0":{"num":0,"name":"0号"}}'::jsonb WHERE id=1;

 

--2）修改person表中id为3，num为12的对象name属性，将name改为“张三”：

UPDATE person t1 SET info = jsonb_set(info, array[(SELECT ORDINALITY::INT - 1 FROM person t2, jsonb_array_elements(info) WITH

ORDINALITY WHERE t1.id = t2.id AND value->>'num' = '12')::text, 'name'::text], '"张三"') WHERE id = 3;

             

              修改person2表中id为3,key为12的对象name属性，将name改为“李四”：

UPDATE person2 SET info = jsonb_set(info,'{12,"name"}'::Text[],'"李四"',TRUE) where id = 3;

      

       --3)删除id为1，info中对象:

              UPDATE person SET info = (info - 0) WHERE id =1;

              UPDATE person2 SET info = (info - '0') WHERE id =1;

3、小结

       本文主要是对常用的JSONB操作做了一个总结，各位同事可以当成一个小手册进行查阅，相信大家在实际的工作应用中已经体会到这种存储方式带来性能优势。在第二部分中对两种格式的JSONB数据进行了操作比较，大家根据比较可以看出，如果在实际应用中需要精确查询或精确操作那么无疑带有键值的JsonObject是较好的选择，如果仅做数据存储和展示JsonArray是较好的选择。