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
– Mike Organek, Commented Jul 4, 2023 at 12:09

Albina · Accepted Answer · 2023-07-04 14:37:58Z

4

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.

answered Jul 4, 2023 at 14:37

Albina

2,0213 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefanov.sm · Accepted Answer · 2023-07-04 14:37:57Z

1

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

answered Jul 4, 2023 at 14:37

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related