Remove object from jsonb array based on value of object-CodePudding

If I define a table like this:

CREATE TABLE test (
  id INT,
  data JSONB
);
INSERT INTO test (id, data) VALUES (1, '[{"a": "x", "b":"b"}, {"c": "y", "b":"b"}]');

How can I remove any object in the array with key "a" that has value "x"?

CodePudding user response：

You can remove these JSON objects by running the following update:

update test set data = new_data.new_data
from (
    select id, json_agg(element) as new_data
    from test,
         jsonb_array_elements(data) as element
    where not element ? 'a' or element ->> 'a' != 'x'
    group by id
) new_data
where new_data.id = test.id

The subquery in new_data removes the objects that contain a key a with a value of x from the data jsonb array. This is done by first unpacking the JSON array so that we can evaluate a condition on them, then doing the filtering, then creating a new JSON array from the objects that were retained. The filtering not element ? 'a' or element ->> 'a' != 'x' consists of two conditions, the first checks if the element doesn't contain an a key, while the second one checks if the value of a is not x. If either evaluates to true, the element is retained.

The result of the subquery is then used to update the original table's data column.

You can see the results on dbfiddle.