CODEWITHSUNDEEP
arraysjsonpostgresqljsonbdelete-record
user3179198
user3179198

Reputation: 29

What is the PostgreSQL call to remove an whole object from a JSONB array of Objects

table: TEST1 jsonB column: COLUMN1 

[{a: 1, b: 2, c:3},
{a: 4, b: 5, c:6},
{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}]

I can locate record with WHERE COLUMN1->>'a' = 4

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

For one or many records

Upvotes: 0

Views: 202

Answers (2)

user3179198
user3179198

Reputation: 29

CREATE TABLE tab( ID INT, jsdata 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)


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

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}]'

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Yes, you can use your filtering logic reversely as WHERE COLUMN1->>'a' != 4 by replacing equality with inequality by contribution of jsonb_array_elements() function, and then apply jsonb_agg() function as below :

SELECT ID, jsonb_agg(elm) AS "Result"
  FROM tab t
 CROSS JOIN jsonb_array_elements(jsdata) AS elm
 WHERE (elm ->> 'a')::int != 4
 GROUP BY ID

Demo

Upvotes: 2

Related Questions