时间戳

首页 > 解决方案 > 将PostgreSQL中的jsonb转换为无循环的行

postgresql - 将PostgreSQL中的jsonb转换为无循环的行

问题描述

ff我的 postgres 数据库中存储了一个 json 数组。第一个表“订单”如下所示：

order_id, basket_items_id
1, {1,2}
2, {3}
3, {1,2,3,1}

第二个表“项目”如下所示：

item_id, price
1,5
2,3
3,20

已经尝试使用多个 sql 加载数据并选择不同的 jsonb 记录，但这不是灵丹妙药。

SELECT
sum(price)
FROM orders
INNER JOIN items on
orders.basket_items_id = items.item_id
WHERE order_id = 3;

想要得到这个作为输出：

order_id, basket_items_id, price
1, 1, 5
1, 2, 3
2, 3, 20
3, 1, 5
3, 2, 3
3, 3, 20
3, 1, 5

或这个：

order_id, sum(price)
1, 8
2, 20
3, 33

标签： postgresql

解决方案

你的orders.basket_items_id列是类型jsonb还是int[]？

如果类型是jsonb您可以使用json_array_elements_text扩展列：

SELECT
    o.order_id,
    o.basket_item_id,
    items.price
FROM
(
    SELECT
        order_id,
        jsonb_array_elements_text(basket_items_id)::int basket_item_id
    FROM
        orders
) o
JOIN
    items ON o.basket_item_id = items.item_id
ORDER BY
    1, 2, 3;

看到这个 DB-Fiddle。

如果类型是int[]（整数数组），您可以使用以下函数运行类似的查询unnest：

SELECT
    o.order_id,
    o.basket_item_id,
    items.price
FROM
(
    SELECT
        order_id,
        unnest(basket_items_id) basket_item_id
    FROM
        orders
) o
JOIN
    items ON o.basket_item_id = items.item_id
ORDER BY 
    1, 2, 3;

看到这个 DB-fiddle

推荐阅读