postgres json_b array does not contain partial object

Question

given a json_b array column named attributes that contains multiple json objects. how can i query the attributes column to get all those rows which does not have a specific value in any json object in the column.

for instance, given the following 4 rows

[{"one": 1}, {"two":2}, {"three": 3, "foo": "foo", "baz": "baz"}]
[{"one": 1}, {"three": 3, "foo": "foo" }]
[{"foo": "foo" }]
[{"one": 1}, {"two":2}, {"three": 3, "baz": "baz"}]

i would like to get all those rows that does not have "foo": "foo" in any object in the array. in the example above, the last row should return.

the array could be converted to text and then use like operator for matching string, but this is error prone and might run longer due to the conversion to text.

Answer 1

You can use the JSONB_ARRAY_ELEMENTS function to unnest the JSON objects within the "attributes" array column and then filter. Try this:

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    attributes JSONB
);

--Sample data
INSERT INTO my_table (attributes) VALUES
('[{"one": 1}, {"two":2}, {"three": 3, "foo": "foo", "baz": "baz"}]'),
('[{"one": 1}, {"three": 3, "foo": "foo" }]'),
('[{"foo": "foo" }]'),
('[{"one": 1}, {"two":2}, {"three": 3, "baz": "baz"}]');

SELECT *
FROM my_table
WHERE NOT EXISTS (
  SELECT 1
  FROM JSONB_ARRAY_ELEMENTS(attributes) AS elem
  WHERE elem->>'foo' = 'foo'
);

I have added dummy data into the table and tried the query and it works as intended.