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

some top level attributes have particular values
particular object in array has required attributes
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'

klin · Accepted Answer · 2016-07-05T12:23:55.820

7

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

edited Jul 05 '16 at 12:23

answered Jul 05 '16 at 11:30

klin

112,967
15
204
232

thanks @klin. I was looking in this direction. However, I've stuck on: if array2 object can be described by 2 attributes, and initial json contains 2 objects in array2 (one with first, another with second), then the only way to query - using join. (I'll add example in the question boby) – Natalia Jul 05 '16 at 12:03
I'm not sure I've understood you well. I've added a note to the answer. – klin Jul 05 '16 at 12:24
the problem: when several attributes are in different objects of the same array. In that case, these objects belong to different row (after flattening by jsonb_array_elements) – Natalia Jul 05 '16 at 12:36
Well, there is no (and couldn't be) a general approach to different object of the same array. Such object just need their individual path. It often happens that with this approach the result set contains duplicate rows. Use then `distinct`, eg.`select distinct on (id) * ... ` – klin Jul 05 '16 at 12:51

score 1 · Answer 2 · answered Jul 05 '16 at 11:37

Expanding on klin's answer, including all the tests:

SELECT j.id
FROM json_test j
     JOIN LATERAL jsonb_array_elements(j.data->'array1') x(y) ON true
     JOIN LATERAL jsonb_array_elements(x.y->'array2') a(b) ON true
WHERE j.data->>'attribute'='0'
  AND x.y->>'id' = 'a12'
  AND a.b->>'attribute' = '22';

Postgres JSONB: where clause for arrays of arrays

2 Answers2

Linked