Postgres JSONB: where clause for arrays of arrays

Question

there is in postgres (v 9.5, if it is matter):

create table json_test(
 id varchar NOT NULL,
 data jsonb NOT NULL,
 PRIMARY KEY(id)
);

Where data is json and contains array of arrays

{
    "attribute": "0",
    "array1": [{
        "id": "a12",
        "attribute": "1",
        "array2": [{
            "id": "a21",
            "attribute": "21"
        }]
    },
    {
        "id": "a12",
        "attribute": "2",
        "array2": [{
            "id": "22",
            "attribute": "22"
        }]
    }]
}

Required:

select id from json_test where 
    json_test->>'attribute'='0' and
    array1.[id='a12'].array2.attribute='22'

Query should mean: give me all ids where

1. some top level attributes have particular values
2. particular object in array has required attributes
3. some object (from array2) in particular array1 has required attributes

the trick is how to implement the last condition.

---

another example:

{
    "attribute": "0",
    "array1": [{
        "id": "a12",
        "attribute": "1",
        "array2": [{
            "id": "a21_1",
            "attribute_1": "21_1"
        },{
            "id": "a21_2",
            "attribute_2": "21_2"
        }]
    }]
}

select * from json_test where 
    array1.[id='a12'].array2.attribute_1='21_1' and  
    array1.[id='a12'].array2.attribute_2='21_2'

Answer 1

The most general way to retrieve nested json arrays is to use multiple jsonb_array_elements() in lateral join. Example:

with json_test(id, data) as (
    values
        (1, 
        '{
            "attribute": "0",
            "array1": [{
                "id": "a12",
                "attribute": "1",
                "array2": [{
                    "id": "a21",
                    "attribute": "21"
                }]
            },
            {
                "id": "a12",
                "attribute": "2",
                "array2": [{
                    "id": "22",
                    "attribute": "22"
                }]
            }]
        }'::jsonb)
    )

select id, elem2
from 
    json_test, 
    jsonb_array_elements(data->'array1') array1(elem1),
    jsonb_array_elements(elem1->'array2') array2(elem2)
where elem2->>'id' = '22';

 id |              elem2              
----+---------------------------------
  1 | {"id": "22", "attribute": "22"}
(1 row)

The method is general because you can easily access any value of any json object on any level, e.g.:

...
where 
    data->>'attribute' = '0'
    and elem1->>'id' = 'a12'
    and elem2->>'id' = 'a21_1';