在PostgreSQL中搜索嵌套的jsonb数组

发布时间：2020-12-13 16:02:38 所属栏目：百科来源：网络整理

导读：我有一个订单表,我将订单摘要存储在jsonb列中 {"users": [ {"food": [{"name": "dinner","price": "100"}],"room": "2","user": "bob"},{"room": "3","user": "foo"} ]} 现在我想用他们的food-名称查询所有用户. 我尝试了以下,但这也给了我用户foo,没有食物.

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

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

现在我想用他们的food->名称查询所有用户.

我尝试了以下,但这也给了我用户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

我该怎么做这样的查询？

UPDATE

我还有两个食物选择这样的夏季

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

而不是我得到：

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

理想情况下我想得到

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

解决方法

好的,如果你这样做的话

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

你得到

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

让我们把这个选择放在另一个,选择我们需要的：

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",{"name" : "breakfast","price" : "50"}],"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
),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} │
└───────────┴────────────────────┘

这是我能想到的最好的.如果你找到更好的方法,请告诉我.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!