PostgreSQL如何从jsonb数组中提取每个id的最后一个非空listinput值

实现思路 #

之前尝试未得到正确结果的核心原因是拆解jsonb数组时没有保留元素的原始顺序，也没有针对过滤后的结果取最后位置的取值。我们可以通过「拆解数组保留原始序号→过滤非空值→按序号倒序取首个」的逻辑实现需求：

正确SQL #

WITH sorted_list AS (
    SELECT 
        t.id,
        (elem ->> 'listinput') AS listinput,
        -- 按id分区，按元素原始位置倒序排序取优先级
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY ordinality DESC) AS rn
    FROM temp.transfers t
    -- 拆解jsonb数组，同时返回元素在原数组中的位置序号
    LEFT JOIN jsonb_array_elements(t.lists) WITH ORDINALITY arr(elem, ordinality) ON true
    -- 过滤listinput不为空的元素，同时兼容原生json null和字符串形式的"NULL"
    WHERE elem ->> 'listinput' IS NOT NULL 
      AND elem ->> 'listinput' <> 'NULL'
)
SELECT id, listinput 
FROM sorted_list 
WHERE rn = 1;

结果验证 #

针对提供的示例数据，执行上述SQL后输出结果和期望完全一致：

补充说明 #

如果业务场景中listinput的空值是PostgreSQL原生的json null，而非字符串"NULL"，删掉AND elem ->> 'listinput' <> 'NULL'这个过滤条件即可。

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