PostgreSQL中拆分JSON产品字段为多行时如何保留orderId？

你的问题核心是CTE子查询未包含orderId字段，导致后续查询无法关联输出该字段。只需在CTE中同时选取orderId和拆分后的JSON元素，就能让每个产品行与原订单ID绑定。

修正后的SQL代码 #

WITH products_array AS (
    -- 同时获取orderId和拆分后的单个产品JSON对象
    SELECT 
        orderId,
        json_array_elements(products) AS product  -- 无需多余的#> '{}'，直接传入products字段即可
    FROM Orders
)
SELECT 
    orderId,
    product #>> '{title}' AS title,
    -- 建议转换为对应数据类型，避免字符串格式的数值
    (product #>> '{amount}')::INTEGER AS amount,
    (product #>> '{price}')::NUMERIC(10,2) AS price
FROM products_array;

关键说明 #

1. 保留orderId关联：在CTE的SELECT中必须包含orderId，这样每个拆分出的产品行才能对应到原订单。
2. 简化JSON操作：移除了多余的#> '{}'，json_array_elements可直接处理products字段的JSON数组类型。
3. 数据类型校正：将amount转为整数、price转为高精度小数，确保字段类型符合业务逻辑，避免后续计算出现问题。

预期输出验证 #

执行上述代码后，将得到你需要的结果：

内容的提问来源于stack exchange，提问作者Sandra G