How can I filter by jsonb object value inside array?

Question

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.

Laurenz Albe · Accepted Answer · 2021-06-01 08:45:50Z

4

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.

edited Jun 1, 2021 at 8:45

answered Jun 1, 2021 at 8:31

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Martin Adámek Over a year ago

Which is not really what I am looking for, this would mean I have to construct the right hand side. In my context the right hand side is just a string (e.g. baz 1), not a JSON.

Martin Adámek Over a year ago

I am looking for a generic solution, as I am targetting multiple databases, not just pg. With mysql something like this should be possible: json_extract(meta, '$.foo[*].bar') = 'baz 1', maybe there is a similar way with some function?

Laurenz Albe Over a year ago

Perhaps a JSONPATH query would make it simpler? Things do become complicated when you use JSON.

Martin Adámek Over a year ago

Yeah also saw that way already, but it is quite problematic for me as, again, I'd have to dynamically construct the right hand side, which can be much more complicated, e.g. with operators. I was looking for a way where the right hand side would stay as simple string like baz 1.

Martin Adámek Over a year ago

Also with this approach, how would nested arrays (of objects) work?

|

Jan Jakeš · Accepted Answer · 2021-06-04 05:44:54Z

0

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"';

edited Jun 4, 2021 at 5:44

answered Jun 1, 2021 at 9:35

Jan Jakeš

2,6991 gold badge15 silver badges10 bronze badges

2 Comments

Martin Adámek Over a year ago

This looks interesting, but I want to be able to use operators, e.g. like or >=, which is apparently not possible this way

Jan Jakeš Over a year ago

@MartinAdámek I see, then you may want to extract the data first using json_path_query and use EXISTS/JOIN/WITH for filtering. See the updated answer.

Collectives™ on Stack Overflow

How can I filter by jsonb object value inside array?

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related