在PostgreSQL中搜索嵌套的jsonb数组

Question

在PostgreSQL中搜索嵌套的jsonb数组

9

我有一个订单表，其中我将订单摘要存储在一个jsonb列中。

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

现在我想查询所有用户的食物名称。我尝试了以下代码，但是它也会给出没有食物的用户foo。

```sql SELECT * FROM users LEFT JOIN food ON users.id = food.user_id WHERE food.name IS NOT NULL; ```

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

- Stefan Mielke

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bruno Calza · Accepted Answer

好的，如果您需要

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

将上述查询重写为使用公共表达式

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

这是我能想到的最好方法。如果你有更好的方式，请告诉我。