Natalia Natalia - 5 months ago 11
JSON Question

Postgres JSONB: where clause for arrays of arrays

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

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