PostgreSQL的jsonb类型中包含数组的 增加、遍历、查找、修改

业务扩展字段在数据库中经常会使用json格式的数据来存储，这就涉及到一个头疼的问题，假设要使用扩展字段里的某个值作为查询条件怎么办，原来PostgreSQL本身就支持这种查询方式。

例子：假设业务扩展字段ext_data存的json格式如下：

我们需要查询扩展字段中extInfo.userType=1的所有数据，那么对应的SQL语句如下：

select * from event_log where (ext_data::json#>>'{extInfo,userType}')::text = '1'

Postgresql 版本 ：9.5

表结构

CREATE TABLE person(id serial, info jsonb);
JSONB结构

[{“num”:“学号”,“name”:“姓名”,“score”:“成绩”}, {“num”:“学号”,“name”:“姓名”,“score”:“成绩”}]

插入

INSERT INTO person (info) VALUES (’[{“num”:“1”,“name”:“张三”,“score”:“90”}]’::jsonb);

增加

UPDATE person SET info = info || ‘[{“num”:“2”,“name”:“李四”,“score”:“91”}]’::jsonb;

遍历

SELECT t.* FROM person, jsonb_to_record(info) AS t(num text, name text, score text) WHERE person.id=1;
或

CREATE TABLE rt_person_info(no text, name text, score text);

SELECT t.* FROM person, jsonb_populate_recordset(null::rt_person_info, info) WHERE person.id=1;

查找

SELECT * FROM person WHERE info @> ‘[{“num”:“1”}]’::jsonb;

修改

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’ = ‘1’)::text, ‘score’::text], ‘“92”’) WHERE id = ‘1’

json里数组是否包含某一个元素

select * from t_user_device WHERE device_json::jsonb->‘ids’?‘111’;