CODEWITHSUNDEEP
postgresql
Martin Adámek
Martin Adámek

Reputation: 18379

How can I filter by jsonb object value inside array?

Let's say I have a jsonb column called meta with this value:

{
  "foo": {
    "bar": "baz"
  }
}

When I want to filter by the meta.foo.bar value, I can do this:

select * from tbl where meta->foo->>bar = 'baz';

Now let's say I want to make the foo an array of objects instead:

{
  "foo": [
    { "bar": "baz 1" },
    { "bar": "baz 2" }
  ]
}

How can I filter by the meta.foo.*.bar values? Tried various combinations of this -> syntax without any luck. Only thing that worked was searching by a specific index in the array, but I want to check all the items, not just one.

FWIW I'd also like to support multiple nested arrays. The use case is filtering by JSON properties in an ORM (http://mikro-orm.io/), so it needs to be dynamic.

Upvotes: 3

Views: 8139

Answers (2)

Jan Jakeš
Jan Jakeš

Reputation: 2669

UPDATE 2: Based on additional information in the comments, if you want to use other operators (e.g. LIKE or >=), then you could do something around the following lines:

SELECT * FROM tbl
WHERE EXISTS (
  SELECT 1
  FROM jsonb_path_query(meta, '$.foo[*].bar') extracted
  WHERE extracted = '"baz 1"'
);

Please note that the filter value must be JSON encoded (therefore the " quotes around the string value).

Similar results can be achieved using JOINs or WITH CTE's but one must be careful about JOIN-style row duplication.

UPDATE 1: Thinking about it, you may want something like this:

SELECT * FROM tbl
WHERE jsonb_path_query_array(meta, '$.foo[*].bar') ? 'baz 1';

My original answer:

Since Postgres 12 you should be able to use the @@ jsonpath operator:

SELECT * FROM tbl WHERE meta @@ '$.foo[*].bar == "baz 1"';

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246308

You can do that with the jsonb containment operator @>:

SELECT '{"foo": [{"bar": "baz 1"}, {"bar": "baz 2"}]}'::jsonb @>
       '{"foo": [{"bar": "baz 1"}]}'::jsonb;

Alternatively, you could use a JSONPATH query:

SELECT jsonb_path_exists(
          '{"foo": [{"bar": "baz 1"}, {"bar": "baz 2"}]}',
          '$.foo[*] ? (@.bar == "baz 2")'
       );

To search for bar no matter how deep it is nested, you can

SELECT jsonb_path_exists(
          '{"foo": [{"bar": "baz 1"}, {"bar": "baz 2"}]}',
          '$.**.bar ? (@ == "baz 2")'
       );

If you want to search with = 'baz 2', you will habe to unnest the array:

SELECT *
FROM jsonb_array_elements(
        '{"foo": [{"bar": "baz 1"}, {"bar": "baz 2"}]}'::jsonb -> 'foo'
     ) AS elem(j)
WHERE j->>'bar' = 'baz 2';

But then you have to know exactly where in the JSON you want to search.

Upvotes: 4

Related Questions