如何对PostgreSQL表的jsonb列应用转换函数生成超链接数组

你可以用PostgreSQL的LATERAL横向连接来逐行处理refs列，完整语句如下：

SELECT 
  COALESCE(
    jsonb_agg(format('<a href="%s">%s</a>', elem ->> 'url', elem ->> 'name')),
    '[]'::jsonb
  ) AS result
FROM MyTable
LEFT JOIN LATERAL jsonb_array_elements(MyTable.refs) elem ON true
-- 如果你的表有主键，把下面的ctid换成主键字段性能更好
GROUP BY MyTable.ctid, MyTable.refs;

逻辑说明 #

• LATERAL关键字允许jsonb_array_elements函数读取当前行的refs列值，逐行展开jsonb数组
• 用LEFT JOIN而非INNER JOIN，保证refs为空数组的行不会被过滤
• COALESCE用来处理空数组场景：空数组展开后没有元素，jsonb_agg会返回null，这时候直接替换为[]空jsonb数组，和你要求的输出一致
• 分组用的ctid是PostgreSQL内置的行唯一标识，如果你自己的表有主键字段，直接替换成主键字段即可，性能更优

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