Question 249890 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres json_b array does not contain partial object

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You can also do this using the jsonb_path_exists function along with an expression:

    FROM your_table
WHERE NOT jsonb_path_exists(
    your_column,
    '$[*] ? (@.foo == "foo")'
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search