Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl(id int, js jsonb);
INSERT INTO tbl (id, js) VALUES
  (1, '[{"ID": "valuea"},{"ID": "valueb"},{"ID": "valuec"}]')
, (2, '[{"ID": "valued"},{"ID": "valuex"}]')
, (3, '[]')
, (4, '{"ID": "valueb"}')
, (5, '{}')
, (6, '{}')
, (9, '[{"ID": "valueb"},{"ID": "valuea"},{"ID": "valueb"},{"ID": "valueb"},{"ID": "valuec"}]')
;

CREATE TABLE



INSERT 0 7


-- demo "-" operator
SELECT t.*,  t.js - j.ord::int
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.js) WITH ORDINALITY j(v,ord) ON j.v = '{"ID": "valueb"}'
WHERE  jsonb_typeof(t.js) = 'array'
AND    t.js @> '[{"ID": "valueb"}]';


id
js
?column?


1
[{"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valuec"}]
[{"ID": "valuea"}, {"ID": "valueb"}]

9
[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]
[{"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]

9
[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]
[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valuec"}]

9
[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]
[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}]



SELECT 4


-- demo json_agg()
SELECT t.*, j.js_new
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT jsonb_agg(j.v) AS js_new
   FROM   jsonb_array_elements(t.js) j(v)
   WHERE  NOT j.v @> '{"ID": "valueb"}'
   ) j
WHERE  t.js @> '[{"ID": "valueb"}]';


id
js
js_new


1
[{"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valuec"}]
[{"ID": "valuea"}, {"ID": "valuec"}]

9
[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]
[{"ID": "valuea"}, {"ID": "valuec"}]



SELECT 2


-- UPDATE removing *single* element
UPDATE tbl t
SET    js = t.js - (SELECT j.ord::int - 1
                    FROM   jsonb_array_elements(t.js) WITH ORDINALITY j(v,ord)
                    WHERE  j.v = '{"ID": "valueb"}'
                    LIMIT  1)
WHERE  t.js @> '[{"ID": "valueb"}]'
AND    jsonb_typeof(t.js) = 'array'
RETURNING *;


id
js


1
[{"ID": "valuea"}, {"ID": "valuec"}]

9
[{"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]



UPDATE 2


-- UPDATE removing *all* elements
UPDATE tbl t
SET    js = (SELECT jsonb_agg(j.v)
             FROM   jsonb_array_elements(t.js) j(v)
             WHERE  NOT j.v @> '{"ID": "valueb"}')
WHERE  t.js @> '[{"ID": "valueb"}]'
AND    jsonb_typeof(t.js) = 'array'
RETURNING *;


id
js


9
[{"ID": "valuea"}, {"ID": "valuec"}]



UPDATE 1


TABLE tbl;


id
js


2
[{"ID": "valued"}, {"ID": "valuex"}]

3
[]

4
{"ID": "valueb"}

5
{}

6
{}

1
[{"ID": "valuea"}, {"ID": "valuec"}]

9
[{"ID": "valuea"}, {"ID": "valuec"}]



SELECT 7

id	js	?column?
1	[{"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valuec"}]	[{"ID": "valuea"}, {"ID": "valueb"}]
9	[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]	[{"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]
9	[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]	[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valuec"}]
9	[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]	[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}]

id	js	js_new
1	[{"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valuec"}]	[{"ID": "valuea"}, {"ID": "valuec"}]
9	[{"ID": "valueb"}, {"ID": "valuea"}, {"ID": "valueb"}, {"ID": "valueb"}, {"ID": "valuec"}]	[{"ID": "valuea"}, {"ID": "valuec"}]

id	js
2	[{"ID": "valued"}, {"ID": "valuex"}]
3	[]
4	{"ID": "valueb"}
5	{}
6	{}
1	[{"ID": "valuea"}, {"ID": "valuec"}]
9	[{"ID": "valuea"}, {"ID": "valuec"}]