How to query objects in nested json in postgres

Question

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.

user330315 · Accepted Answer · 2019-04-04 09:19:19Z

3

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

answered Apr 4, 2019 at 9:19

user330315

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

2 Comments

Martin Over a year ago

Oh I can use nested query to iterate over jsonb_each output. Got it. And there is no way to do it in one query?

user330315 Over a year ago

@MartinZinovsky: that is one query

Collectives™ on Stack Overflow

How to query objects in nested json in postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related