How to query objects in nested json in postgres

Lets say I have table called users with jsonb column called attrs with values like this:

{
  "uuid5": {
    "label": "Email",
    "value": "[email protected]"
  },
  "uuid6": {
    "label": "Last Name ",
    "value": "Yang"
  }
}

Here is a one-liner:

"attrs": { "uuid5": { "label": "Email", "value": "[email protected]" }, "uuid6": { "label": "Last Name ", "value": "Yang" }

As you can see there are uniq keys uuid5, uuid6 and so on.

How to get users with label = 'Email' and value = '[email protected]'?

In postgres docs about json functions there is a function called jsonb_each which returns set of JSON object key/value pairs. But I could not find a way to write a query based on that.

Solution

You need jsonb_each to iterate over all entries in the attrs colum. It will return key/value pairs where the key is the uuid and the entry is your actual JSON structure you want to inspect. You can use that in conjunction with an EXISTS condition:

select u.*
from users u
where exists (select *
              from jsonb_each(u.attrs) as t(uid,entry)
              where t.entry ->> 'label' = 'Email'
                and t.entry ->> 'value' = '[email protected]')

Online example: https://rextester.com/SHN95362