从JSONB对象数组中删除整个对象的PostgreSQL调用是什么 [英] What is the PostgreSQL call to remove an whole object from a JSONB array of Objects

问题描述

表:TEST1 jsonB列:COLUMN1

table: TEST1 jsonB column: COLUMN1

[{a: 1, b: 2, c:3},
{a: 4, b: 5, c:6},
{a: 7, b: 8, c:9}]

我想在a = 4的值上进行选择，并删除作为结果一部分的整个对象，如下所示 [{a: 1, b: 2, c:3}, {a: 7, b: 8, c:9}]

I want to select on the value of a = 4 and remove the whole object it is part of for result as below [{a: 1, b: 2, c:3}, {a: 7, b: 8, c:9}]

我可以使用WHERE COLUMN1->>'a' = 4

但是我无法弄清楚或如何找到这个基本用例:例如，将数组中的对象作为JSONB列的一部分删除

but I have not been able to figure out or how to find this basic use case: eg delete an object from an array as part of a JSONB column

用于一个或多个记录

推荐答案

创建表"选项卡(ID INT，jsdata jsonb)

CREATE TABLE tab( ID INT, jsdata jsonb )

插入"选项卡值" (1，"[{{a":1，" b":2，"c":3}，{"a":4，"b":5，"c":6} ，{"a":7，"b":8，"c":9}]':: jsonb)， (2，"[{{a":4"，"b":2，"c":3}，{"a":9，"b":5，"c":6} ，{"a":7，"b":8，"c":9}]':: jsonb)

INSERT INTO tab VALUES (1,'[{"a": 1, "b": 2, "c":3},{"a": 4, "b": 5, "c":6},{"a": 7, "b": 8, "c":9}]'::jsonb), (2,'[{"a": 4, "b": 2, "c":3},{"a": 9, "b": 5, "c":6},{"a": 7, "b": 8, "c":9}]'::jsonb)

更新"标签t1 SET jsdata =(选择jsonb_agg(elm) 从选项卡t2 交叉加入jsonb_array_elements(jsdata)AS榆木 在哪里(ELM->>'a'):: int！= 4 AND t2.ID = 2 GROUP BY t2.ID) t1.ID = 2

UPDATE tab t1 SET jsdata = ( SELECT jsonb_agg(elm) FROM tab t2 CROSS JOIN jsonb_array_elements(jsdata) AS elm WHERE (elm->>'a')::int != 4 AND t2.ID = 2 GROUP BY t2.ID ) WHERE t1.ID = 2

结果: 1"，"[{a":1，"b":2，"c":3}，{"a":4，"b":5，"c":6}， {"a":7，"b":8，"c":9}]"， 2"，"[{{a":9，"b":5，"c":6}，{"a":7，"b":8，"c":9}] '

RESULT: 1,'[{"a": 1, "b": 2, "c":3},{"a": 4, "b": 5, "c":6},{"a": 7, "b": 8, "c":9}]', 2,'[{"a": 9, "b": 5, "c":6},{"a": 7, "b": 8, "c":9}]'

这篇关于从JSONB对象数组中删除整个对象的PostgreSQL调用是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！