Filter query by an element from inner json array

Question

Simple table in PostgreSQL 12.3

create table aaa(id integer, data json);

In data column stores JSON with the following form:

{
    "foo": 1,
    "persons": [
        {
            "name": "2fdsdsd",
            "test": {
                "id": "123",
                "age": 32
            }
        }
    ],
    "some_prop": "123"
}

I need to find all records from aaa where test.id = '123' or where test = null.

It's integer not indeger. Your example json is invalid because of unmatched quotes. Are you actually using json type or jsonb? What have you tried so far? By second one you mean those records where test.id is either '123' or null? — Zegarek
– Zegarek, Commented Oct 9, 2023 at 19:08

Zegarek · Accepted Answer · 2023-10-09 19:23:10Z

1

If that's jsonb, you can use @? operator with a jsonpath: demo

select id, jsonb_pretty(data) from aaa
where data @? '$.persons[*].test?(@.id=="123" || @==null)';

Which means roughly

Take what's under persons key
Assume it's an array and inspect all its elements
In each element check test key
Under test, see if value for key id is '123' or if the whole test is null.

If it's a plain json, you can cast it:

select id, jsonb_pretty(data::jsonb) from aaa
where data::jsonb @? '$.persons[*].test?(@.id=="123" || @==null)';

answered Oct 9, 2023 at 19:23

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

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

1 Comment

JD_UA Over a year ago

this is exactly what i need, tnx @Zegarek

Collectives™ on Stack Overflow

Filter query by an element from inner json array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related