PostgreSQL 11.13中过滤JSON数组并返回指定属性的实现方案

需要从表aosqe_ema_tools.ocs_response的JSONB字段ocsjsonb中，筛选出paymentDetails数组里包含installmentType = 'STANDARD'的记录，同时返回processedResult.region属性，以及将processedResult.TYPE重命名为type的列，最终输出符合指定格式。

正确SQL实现 #

SELECT
    t.ocsjsonb ->> 'processedResult' ->> 'region' AS region,
    t.ocsjsonb ->> 'processedResult' ->> 'TYPE' AS type
FROM aosqe_ema_tools.ocs_response t
CROSS JOIN jsonb_array_elements(t.ocsjsonb -> 'processedResult' -> 'orderPayment' -> 'paymentDetails') AS arr(item_object)
WHERE arr.item_object -> 'installmentPayment' ->> 'installmentType' = 'STANDARD';

代码说明 #

• 使用CROSS JOIN jsonb_array_elements展开paymentDetails数组，遍历其中每个元素
• 通过WHERE子句过滤出数组元素中installmentPayment.installmentType等于STANDARD的记录
• 直接从原JSONB字段提取上层的processedResult.region和processedResult.TYPE字段，并用AS指定列名，匹配期望的输出结构

原SQL问题说明 #

你之前的SQL仅返回了展开后的paymentDetails数组元素，未提取需要的region和type字段，也未添加过滤条件，因此无法满足需求。

内容的提问来源于stack exchange，提问作者Ilyas Patel