Searching nested jsonb array in PostgreSQL

All right, if you do

SELECT jsonb_array_elements(summary->'users') as users FROM orders;

you get

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

Let's put this select inside another one, selecting what we need:

SELECT users->'user' as user_name, users->'food'->0->'name' as food FROM (
    SELECT jsonb_array_elements(summary->'users') as users FROM orders
) as s;

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

We're close. We just need to add a WHERE.

SELECT users->'user' as user_name, users->'food'->0->'name' as food FROM (
    SELECT jsonb_array_elements(summary->'users') as users FROM orders
) as s WHERE (users->'food') is not null;

Resulting in

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

If you have more data in your 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' as user_name, jsonb_array_elements(users->'food')->>'name' as food FROM (
    SELECT jsonb_array_elements(summary->'users') as users FROM orders
) as s WHERE (users->'food') is not null;

And

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

Rewriting the above query to use Common Table Expressions

WITH users_data AS (
    SELECT jsonb_array_elements(summary->'users') as users FROM orders
), user_food AS (
    SELECT users->'user' as user_name, jsonb_array_elements(users->'food')->>'name' as food 
    FROM users_data
    WHERE (users->'food') is not null  
) SELECT * FROM user_food;

Now we just need to group by user_name

WITH users_data AS (
    SELECT jsonb_array_elements(summary->'users') as users FROM orders
), user_food AS (
    SELECT users->'user' as user_name, jsonb_array_elements(users->'food')->>'name' as food 
    FROM users_data
    WHERE (users->'food') is not null  
) SELECT user_name, array_agg(food) foods FROM user_food GROUP BY user_name;

Final result

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

That's the best I could come up with. Let me know if you find a better way.