Sublime Forum

Remove values from array attribute of JSON column

#1

0

I have a json (not jsonb) column in a PostgreSQL (version 13.12) table from which I want to remove specific values of an array attribute.

For example, in the following table (db-fiddle) I want to remove all occurrences of “D” from the actions attributes.

DROP TABLE IF EXISTS test;
CREATE TABLE test (
data JSON
);
INSERT INTO test VALUES
(’{“name”: “Alice”, “actions”: [“B”, “C”, “D”]}’),
(’{“name”: “Charles”, “actions”: [“C”, “D”, “E”]}’);
SELECT * FROM test;
How can I do this with a query?

0 Likes
#2

ChatGPT or other similar AI services are good at this kind of questions.

I know nothing about PostgreSQL, but just copy and paste your thread to ChatGPT and it says

UPDATE test
SET data = jsonb_set(data::jsonb, '{actions}', 
                     to_jsonb(
                       (SELECT json_agg(elem) 
                        FROM jsonb_array_elements(data::jsonb -> 'actions') AS elem 
                        WHERE elem::text <> '"D"')
                     )::json)
WHERE data::jsonb -> 'actions' @> '["D"]';
0 Likes