Question 200366 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search