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.

Answer 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

1. Take what's under persons key
2. Assume it's an array and inspect all its elements
3. In each element check test key
4. 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)';