PostgreSQL jsonb array of objects: how to filter by one of the fields and get the value of the other one

Question

Suppose we have two columns: id, params. params is a jsonb column that contains arrays of objects like:

[
    {
        "prop": "a",
        "answer": "123"
    },
    {
        "prop": "b",
        "answer": "456"
    }
]

I need to create a query that returns id + answer where prop has some specific value (so at most one row per id).

For example, you can filter by prop=a, and in this case, you'll get 123 for that particular row. Other rows (with other ids) can have a different value or don't have it all (since there could be no prop=a in their jsons).

I tried some solutions with jsonb_array_elements(params) but I always ended up having all possible values from the json even if you have prop=a only in one of the elements (so id is duplicated in the result set with each answer). Also, I obviously cannot use solutions based on ordinality of the elements in the arrays.

Use `jsonb_array_elements()` in a lateral join and then filter the exploded array with a `where`. — Mike Organek, Jul 04 '23 at 12:09

score 1 · Answer 1 · answered Jul 04 '23 at 14:37

First flatten the table and then filter as suggested by Mike Organek. the_table CTE is a mimic of a real data table.

with the_table(id, params) as
(
 values
 (1, '[{"prop": "a", "answer": "123"},{"prop": "b", "answer": "456"}]'::jsonb),
 (2, '[{"prop": "a", "answer": "124"},{"prop": "b", "answer": "457"}]'),
 (3, '[{"prop": "a", "answer": "124"},{"prop": "c", "answer": "458"}]')
)
select id, j ->> 'answer' answer
from the_table cross join lateral jsonb_array_elements(params) j
where j ->> 'prop' = 'b';

id	answer
1	456
2	457

score 1 · Accepted Answer · answered Jul 04 '23 at 14:37

As it was suggested by Mike Organek, you can use jsonb_array_elements() function.
The final query can be as follows:

select id, 
       tmp.parameters->>'prop' AS property, 
       tmp.parameters->>'answer' AS answer
from data
left join LATERAL jsonb_array_elements(data.params) 
          WITH ORDINALITY AS tmp (parameters, row_number) ON true
where tmp.parameters->>'prop' = 'a';

Lateral join unwrappes jsonb object (top-level elements) and you get separate columns corresponding for 'prop' and 'answer' properties in a jsonb column.
Use where clause to filter the rows the way you need.

Here is a demo.

Also more can be found in this answer.

PostgreSQL jsonb array of objects: how to filter by one of the fields and get the value of the other one

2 Answers2