Поиск вложенного массива jsonb в PostgreSQL

У меня есть таблица заказов, в которой я храню сводку заказа в столбце jsonb.

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

Теперь я хочу запросить все users с их food->name.

Я попробовал следующее, но это также дало мне пользователя foo, у которого нет еды.

select 
  jsonb_array_elements(jsonb_array_elements(summary->'users')->'food')->>'name'  as food, 
  jsonb_array_elements(summary->'users')->>'user' as user_name 
from orders;

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

Как бы я сделал такой запрос?

ОБНОВИТЬ

У меня тоже есть такой летний с двумя вариантами еды

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

и чем я получаю:

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

в идеале я хочу получить

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

postgresql jsonb
person Stefan Mielke    schedule 19.08.2015    source источник

Ответы (1)


arrow_upward
7
arrow_downward

Хорошо, если ты сделаешь

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

Вы получаете

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

Поместим этот select внутрь другого, выбрав то, что нам нужно:

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)   │
└───────────┴──────────┘

Мы близко. Нам просто нужно добавить 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;

В результате чего

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

Если у вас есть больше данных в вашем массиве еды, например

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

Ты можешь сделать

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;

А также

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

Переписав приведенный выше запрос, чтобы использовать 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;

Теперь нам просто нужно сгруппировать по 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;

Конечный результат

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

Это лучшее, что я мог придумать. Дайте мне знать, если вы найдете лучший способ.

person Bruno Calza    schedule 19.08.2015
comment
это здорово!, а что, если в еде есть еще такие элементы, как "food": [{"name": "test1", "price": "100"}, {"name": "breakfast", "price": "10"}] - person Stefan Mielke; 20.08.2015