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

待我称王封你为后i 2024-04-17 19:17 29阅读 0赞

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

例子：假设业务扩展字段ext\_data存的json格式如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTEzNzYyMzE2MA_size_16_color_FFFFFF_t_70]  
我们需要查询扩展字段中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’;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTEzNzYyMzE2MA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/f460c1b1ef9a48e8b0b0bfe77678716d.png