Postgres jsonb: querying multi level

Question

In a Postgres (9+) table there is a column of type jsonb with the following json:

{
   "dynamicFields":[
      {
         "name":"040",
         "subfields":[
            {
               "name":"a",
               "value":"abc"
            },
            {
               "name":"a",
               "value":"xyz"
            }
         ]
      }
   ]
}

I would like to write a query that return only the rows where the field name equals 040 and subfield a equals xyz.

This is as far as I got, so far:

select e.obj from my_table
cross join lateral jsonb_array_elements(my_column-> 'dynamicFields') as e(obj)
where e.obj ->> 'name' = '040' and e.obj ->> 'subfields' @> '{"name": "a", "value": "xyz"}'::jsonb

How should this query be to achieve this?

"9+" covers 13 different major versions 5 from them no longer being supported (and 3 of them do not even have jsonb) . Which version are you really using? — user330315
– user330315, Commented Jun 28, 2021 at 5:35

Bergi · Accepted Answer · 2021-06-09 21:30:12Z

3

e.obj ->> 'subfields' has a text result. You'll want to use e.obj -> 'subfields' that returns the jsonb value where the @> operator works. Also the containment checks needs to have another array as the right hand side, so that it will test whether all values in the right array are contained in the left array - it doesn't work to pass the element object directly.

select e.obj from my_table
cross join lateral jsonb_array_elements(my_column-> 'dynamicFields') as e(obj)
where e.obj ->> 'name' = '040' and e.obj -> 'subfields' @> '[{"name": "a", "value": "xyz"}]'::jsonb
--                                        ^                 ^                             ^

^{(online demo)}

edited Jun 9, 2021 at 21:30

answered Jun 9, 2021 at 20:35

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Comments

user330315 · Accepted Answer · 2021-06-28 05:44:39Z

0

As you have an equality condition you can use the "contains" operator directly by providing a JSON value of what you want. There is no need to unnest the arrays.

select *
from my_table
where my_column -> 'dynamicFields' @> '[{"name": "040", "subfields": [{"name":"a", "value": "xyz"}]}]'

Starting with Postgres 12 an alternative is to a SQL/JSON path operator:

select *
from my_table
where my_column @? '$.dynamicFields[*] ? (@.name == "040").subfields[*] ? (@.name == "a" && @.value == "xyz")'

answered Jun 28, 2021 at 5:44

user330315

Collectives™ on Stack Overflow

Postgres jsonb: querying multi level

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related