Searching nested jsonb array in PostgreSQL -

i have orders table store summary of order in jsonb column

 {"users": [    {"food": [{"name": "dinner", "price": "100"}], "room": "2", "user": "bob"},     {"room": "3", "user": "foo"}  ]} 

now want query users food->name.

i tried following, gives me user foo, has no food.

select    jsonb_array_elements(jsonb_array_elements(summary->'users')->'food')->>'name'  food,    jsonb_array_elements(summary->'users')->>'user' user_name  orders;   food  | user_name   -------+-----------  dinner | bob  dinner | foo 

how such query?

---

update

i have summery 2 food options

{"users": [   {"food": [{"name": "dinner", "price": "100"}, {"name": "breakfast", "price": "100"}], "room": "2", "user": "bob"},    {"room": "3", "user": "foo"}  ]} 

and get:

   food    | user_name  -----------+-----------  dinner    | bob  breakfast | foo 

ideally want get

   food               | user_name  ----------------------+-----------  dinner, breakfast    | bob 

all right, if do

select jsonb_array_elements(summary->'users') users orders; 

you get

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │                                                      users                                                       │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ {"food": [{"name": "dinner", "price": "100"}, {"name": "breakfast", "price": "50"}], "room": "2", "user": "bob"} │ │ {"room": "3", "user": "foo"}                                                                                     │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 

let's put select inside one, selecting need:

select users->'user' user_name, users->'food'->0->'name' food (     select jsonb_array_elements(summary->'users') users orders ) s;  ┌───────────┬──────────┐ │ user_name │   food   │ ├───────────┼──────────┤ │ "bob"     │ "dinner" │ │ "foo"     │ (null)   │ └───────────┴──────────┘ 

we're close. need add where.

select users->'user' user_name, users->'food'->0->'name' food (     select jsonb_array_elements(summary->'users') users orders ) s (users->'food') not null; 

resulting in

┌───────────┬──────────┐ │ user_name │   food   │ ├───────────┼──────────┤ │ "bob"     │ "dinner" │ └───────────┴──────────┘ 

if have more data in food array like

'{"users": [{"food": [{"name": "dinner", "price": "100"}, {"name" : "breakfast", "price" : "50"}], "room": "2", "user": "bob"}, {"room": "3", "user": "foo"}]}' 

you can do

select users->'user' user_name, jsonb_array_elements(users->'food')->>'name' food (     select jsonb_array_elements(summary->'users') users orders ) s (users->'food') not null; 

and

┌───────────┬───────────┐ │ user_name │   food    │ ├───────────┼───────────┤ │ "bob"     │ dinner    │ │ "bob"     │ breakfast │ └───────────┴───────────┘ 

rewriting above query use common table expressions

with users_data (     select jsonb_array_elements(summary->'users') users orders ), user_food (     select users->'user' user_name, jsonb_array_elements(users->'food')->>'name' food      users_data     (users->'food') not null   ) select * user_food; 

now need group user_name

with users_data (     select jsonb_array_elements(summary->'users') users orders ), user_food (     select users->'user' user_name, jsonb_array_elements(users->'food')->>'name' food      users_data     (users->'food') not null   ) select user_name, array_agg(food) foods user_food group user_name; 

final result

┌───────────┬────────────────────┐ │ user_name │       foods        │ ├───────────┼────────────────────┤ │ "bob"     │ {dinner,breakfast} │ └───────────┴────────────────────┘ 

that's best come with. let me know if find better way.