Filter for property in array of arrays of objects in jsonb column

Question

In my postgresql database, I have a jsonb column that stores data as follows.

"Info": {
    "Groups": [
         {
             "Name": "grp1",
             "Type": "Simple"
             "Users": [
                {
                    "FirstName": "John"
                }
             ]
         }
    ]
}

It's basically an array of Group objects with another array of objects property Users.

I can query a group by name using the "JsonContains" operator, e.g.

-- get all groups with Type = "Simple"
WHERE attrs#>'Info,Groups' @> '[{"Type": "Simple"}]'

However, I can't seem to figure out how to get all groups with users where a user's first name is John.

Here's what I've tried (doesn't work)

WHERE attrs#>'Info,Groups,Users' @> '[{"FirstName": "John"}]'

Any help is greatly appreciated. Note that I'm currently running on PostgreSQL version 10.7

user330315 · Accepted Answer · 2022-08-24 05:32:05Z

2

Users is an array, so you need to provide another array for the contains operator:

WHERE attrs #> '{Info,Groups}' @> '[{"Users": [{"FirstName": "John"}]}]'

Alternatively you can use a JSON path condition:

WHERE attrs #> '{Info,Groups}' @@ '$.Users[*].FirstName == "John"'

answered Aug 24, 2022 at 5:32

user330315

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

Collectives™ on Stack Overflow

Filter for property in array of arrays of objects in jsonb column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest