Postgres 15

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

CREATE TABLE tbl (
  id int
, favorites jsonb
);

INSERT INTO tbl VALUES
  (0, null)                                     -- will not change
, (1, '{"shapes": ["Triangle", "Square", "Rectangle", "Circle"]}')
, (2, '{"shapes": ["Rectangle", "Triangle", "Circle"]}')
, (3, '{"shapes": ["Rectangle", "Triangle"]}')  -- will not change
;

CREATE TABLE



INSERT 0 4


SELECT ctid, * FROM tbl;


ctid
id
favorites


(0,1)
0
null

(0,2)
1
{"shapes": ["Triangle", "Square", "Rectangle", "Circle"]}

(0,3)
2
{"shapes": ["Rectangle", "Triangle", "Circle"]}

(0,4)
3
{"shapes": ["Rectangle", "Triangle"]}



SELECT 4


SELECT id, favorites
     , favorites -> 'shapes'
     , (favorites -> 'shapes') - '{Square, Circle}'::text[]
     , jsonb_set(favorites, '{shapes}', (favorites -> 'shapes') - '{Square, Circle}'::text[])
FROM   tbl;


id
favorites
?column?
?column?
jsonb_set


0
null
null
null
null

1
{"shapes": ["Triangle", "Square", "Rectangle", "Circle"]}
["Triangle", "Square", "Rectangle", "Circle"]
["Triangle", "Rectangle"]
{"shapes": ["Triangle", "Rectangle"]}

2
{"shapes": ["Rectangle", "Triangle", "Circle"]}
["Rectangle", "Triangle", "Circle"]
["Rectangle", "Triangle"]
{"shapes": ["Rectangle", "Triangle"]}

3
{"shapes": ["Rectangle", "Triangle"]}
["Rectangle", "Triangle"]
["Rectangle", "Triangle"]
{"shapes": ["Rectangle", "Triangle"]}



SELECT 4


UPDATE tbl
SET    favorites = jsonb_set(favorites, '{shapes}', (favorites -> 'shapes') - '{Square, Circle}'::text[])
WHERE  favorites IS DISTINCT FROM jsonb_set(favorites, '{shapes}', (favorites -> 'shapes') - '{Square, Circle}'::text[])
RETURNING *;


id
favorites


1
{"shapes": ["Triangle", "Rectangle"]}

2
{"shapes": ["Rectangle", "Triangle"]}



UPDATE 2


-- note the untouched rows!
SELECT ctid, * FROM tbl;


ctid
id
favorites


(0,1)
0
null

(0,4)
3
{"shapes": ["Rectangle", "Triangle"]}

(0,5)
1
{"shapes": ["Triangle", "Rectangle"]}

(0,6)
2
{"shapes": ["Rectangle", "Triangle"]}



SELECT 4

ctid	id	favorites
(0,1)	0	null
(0,2)	1	{"shapes": ["Triangle", "Square", "Rectangle", "Circle"]}
(0,3)	2	{"shapes": ["Rectangle", "Triangle", "Circle"]}
(0,4)	3	{"shapes": ["Rectangle", "Triangle"]}

id	favorites
1	{"shapes": ["Triangle", "Rectangle"]}
2	{"shapes": ["Rectangle", "Triangle"]}