PostgreSQL：从JSONB对象数组中获取首个非空值（COALESCE）

嘿，这个问题我熟！你一开始的思路是对的，但确实踩了PostgreSQL里集合返回函数（SRF）的坑——这类函数不能直接放在COALESCE这种单值函数里，官方提示的LATERAL JOIN就是解决这个问题的关键，咱们来一步步搞定它。

方法一：用LATERAL JOIN筛选并取第一个值 #

这是兼容性最好的方案，适合所有支持JSONB的PostgreSQL版本：

单条数据场景 #

如果只需要从单条记录的JSONB数组里取第一个非null的type，可以这么写：

SELECT elem ->> 'type' AS first_non_null_type
FROM your_table,
     LATERAL jsonb_array_elements(jsonb_column -> 'data') AS elem
WHERE elem ->> 'type' IS NOT NULL
LIMIT 1;

• LATERAL jsonb_array_elements(...)会把每条记录的data数组拆分成单独的行（每个数组元素一行）
• WHERE条件过滤掉type为null的元素
• LIMIT 1直接取第一个符合条件的type值

全表每条记录对应场景 #

如果要给原表的每一行都返回对应的第一个非nulltype（哪怕没有就返回null），用带子查询的LATERAL LEFT JOIN：

SELECT 
    t.id, -- 这里替换成你的表主键或其他需要保留的字段
    elem ->> 'type' AS first_non_null_type
FROM your_table t
LEFT JOIN LATERAL (
    SELECT value
    FROM jsonb_array_elements(t.jsonb_column -> 'data')
    WHERE value ->> 'type' IS NOT NULL
    LIMIT 1
) elem ON true;

用LEFT JOIN保证原表的每一行都能出现在结果里，哪怕对应的data数组里没有非null的type。

方法二：用JSONPath（PostgreSQL 12+） #

如果你的PostgreSQL版本是12或以上，可以用更简洁的JSONPath语法，不用展开数组：

SELECT 
    jsonb_path_query_first(jsonb_column, '$.data[*] ? (@.type != null).type') ->> '0' 
    AS first_non_null_type
FROM your_table;

这个JSONPath表达式的意思是：

• $.data[*]：遍历data数组的所有元素
• ? (@.type != null)：筛选出type不为null的元素
• .type：提取这些元素的type字段
• jsonb_path_query_first直接返回第一个符合条件的值

为什么你之前的写法报错？ #

jsonb_array_elements是返回多行结果的函数，而COALESCE只能处理单个值，PostgreSQL不允许在单值函数里嵌套集合返回函数，这就是你看到ERROR: set-returning functions are not allowed in COALESCE的原因。把集合返回函数放到LATERAL JOIN里，就能把多行结果转换成可以逐行处理的数据集了。

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