Postgresql 处理jsonb字段

参考文档

https://www.cnblogs.com/zhangfx01/p/9506219.html

https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype/23500670#23500670

一、查询jsonb字段

select id,jsonb_v::jsonb->>'celldata' ,jsonb_v->>'celldata'  from t_user where id=35;

update t_user set jsonb_v=jsonb_v||'{"address":"上海浦东"}'::jsonb where id=35;  -- 添加address

update t_user set jsonb_v=jsonb_v||'{"address":"被冻结上海浦东"}'::jsonb where id=35;  -- 修改address的值

update t_user set jsonb_v=jsonb_v - 'address' where id=35; -- 删除address

2、复杂对象

{
    "myinfo": {
        "name": "xc",
        "myaddress": {
            "city": "上海",
            "pre": "区"
        }
    }
}
{"myinfo":{"name":"xc","myaddress":{"city":"上海","pre":"区"}}}

-- 添加到根节点下

update t_user set jsonb_v=jsonb_v||'{"myinfo":{"name":"xc","myaddress":{"city":"上海","pre":"区"}}}'::jsonb where id=35;

-- 增加一个内嵌age字段 {"myinfo":{"myaddress":{"age":12}}}

{
    "myinfo": {
        "myaddress": {
            "age": 12
        }
    }
}

-- 错误！ 以下方法把整个myinfo字段都作为修改

update t_user set jsonb_v=jsonb_v||'{"myinfo":{"myaddress":{"age":12}}}'::jsonb where id=35;  

-- 对myinfo->myaddress 增加一个age键 （其中true的作用为，没有就新增一个键）

update t_user set jsonb_v= jsonb_set(jsonb_v,'{myinfo,myaddress,age}'::text[],'"124"',true) where id=35;

update t_user set jsonb_v= jsonb_set(jsonb_v,'{myinfo,myaddress,age}'::text[],'12',true) where id=35;

3、List

-- 增加一个集合

update t_user set jsonb_v=jsonb_v||'{"celldata":[]}'::jsonb where id=35;

-- 数组中插入一条记录

update t_user set jsonb_v=jsonb_insert(jsonb_v::jsonb,'{celldata,0}','{"c":1,"r":1,"v":{"con":"str"}}',false) where id =35;

-- 删除集合

update t_user set jsonb_v=jsonb_v - 'celldata' where id=35;

-- 将celldata集合中第二个的v值整体改变

update t_user set jsonb_v= jsonb_set(jsonb_v,'{celldata,1,v}'::text[],'{"con":"new str"}',true) where id=35;

-- 将celldata集合中第二个v的某个key的值改变

update t_user set jsonb_v= jsonb_set(jsonb_v,'{celldata,1,v,con1}'::text[],'"new str"',true) where id=35;

-- 删除celldata集合中的第二个

update t_user set jsonb_v = jsonb_v #- '{celldata,1}' where id=35;

-- 集合在其他层级添加

update t_user set jsonb_v=jsonb_v||'{"myinfo":{"celldata":[]}}'::jsonb where id=35;

-- 集合在其他层级添加元素

update t_user set jsonb_v=jsonb_insert(jsonb_v,'{myinfo,celldata,0}','{"c":1,"r":1,"v":{"con":"str"}}',false) where id =35;

-- 集合在其他层级添加元素到集合尾部

update t_user set jsonb_v=jsonb_insert(jsonb_v,'{myinfo,celldata,-1}','{"c":21,"r":21,"v":{"con":"str"}}',true) where id =35;

-- 集合在其他层级元素修改

update t_user set jsonb_v= jsonb_set(jsonb_v,'{myinfo,celldata,0,v,con}'::text[],'"new str"',true) where id=35;

-- 查询时返回修改过数据

select
    jsonb_agg(
        case when c1 @> '{"c":21,"r":21}'
        then '{"c":21,"r":21,"v":"vasfaf"}'
        else c1 end)
from
    (
        select jsonb_array_elements(jsonb_v->'myinfo'->'celldata') c1 
        from t_user where id=35
    ) as t;

4、创建索引

Create index 索引名 on 表名(字段名)
-- jsonb字段创建索引
create index idx_ay_json_v on ay_json_test using gin(json_value jsonb_path_ops );

5、创建自增ID

CREATE SEQUENCE gys.mytable_myid_seq
CYCLE
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 99999999
    CACHE 1;

ALTER SEQUENCE gys.mytable_myid_seq
OWNER TO postgres;

sql使用时

String sql = "insert into t_user (id,username,password,age,remark,updatetime,jsonb) values " +
        " (nextval('ay_json_test_id_seq'),?,?,?,?,now(),?)";

6、对jsonb字段中的key进行排序查询

select * from ay_json_test order by json_value::json->>'agent' desc
order by jsonb字段名::json->>key desc/asc

7、对jsonb字段中的key进行筛选

select * from ay_json_test where json_value @>'{"agent":"02"}'::jsonb;
Where jsonb字段名 @>’json串’::jsonb

8、jsonb字段返回的结果只获取其中几个key

-- 从普通字段以及jsonb字段中获取指定的几个key返回结果

select id,json_value::json->>'agent' as agent,json_value::json->>'ay_name' as ay_name from ay_json_test t ;

-- 从普通字段以及jsonb字段中获取指定的几个key返回json结果

select row_to_json(a.*) from (

select json_value::json->>'agent' as agent ,json_value::json->>'ay_name' as ay_name from ay_json_test t

) a;

9、按页返回数据 2个方法效率未知

-- 按ay_name 排序获取第4条记录开始的记录

-- 方法一

SELECT a.* FROM
( SELECT t.*, ROW_NUMBER() over(ORDER BY json_value :: json ->> 'an_name' ) AS rn FROM ay_json_test t ) a
WHERE rn > 3 and rn<6

-- 方法二

select * from ay_json_test order by json_value::json->>'ay_name'  limit 2 offset 3

10、获取数组元素数量

select jsonb_array_length(jsonb_v->'myinfo'->'celldata')  from t_user where id=35 ;
-- 或者
select jsonb_array_length(c1) from  (
    select jsonb_v->'myinfo'->'celldata' as c1 from t_user where id=35 
) as t;

11、获取jsonb字段长度

select jsonb_v as v,
CHAR_LENGTH(jsonb_v :: text) as l,
LENGTH(jsonb_v :: text) as l2,
CHARACTER_LENGTH(jsonb_v :: text) as l3,
octet_length(jsonb_v :: text) as l4
from t_user;

12、复杂处理

C=1 r=2 修改v

Insert into t_user(id,jsonb_v) values(113,’[{"c":2,"r":2,"v":{"con":"str"}},{"c":1,"r":1,"v":{"con":"new str"}},{"c":21,"r":21,"v":{"con":"str"}},{"c":20,"r":21,"v":{"con":"str"}}]’);

先分拆数据，得到最终结果

select jsonb_agg(col2) from 
(
  select row_to_json(t2) col2 from
  (
    select c, r , 
      case when c=20 and r=21  then '{"k":1,"y":"2"}'
      else v end v
    from 
          (select jsonb_v  from t_user where id=113) as t1,
          jsonb_to_recordset ( t1.jsonb_v ) x ( C INT, r INT, v jsonb )
  ) t2
) t3

c>=20 c=c+20

select jsonb_agg(col2) from 
(
  select row_to_json(t2) col2 from
  (
    select c,
      case when r>= 20 then r+20
      else r end r,
      v
    from 
          (select jsonb_v  from t_user where id=113) as t1,
          jsonb_to_recordset ( t1.jsonb_v ) x ( C INT, r INT, v jsonb )
  ) t2
) t3

完