如何在PostgreSQL中将JSONB动态字段提取为指定列格式？

要实现将JSONB字段中的指定数据展开为id、attributtename、value三列的格式，可以通过jsonb_each_text函数展开顶级字段，再单独提取嵌套的label字段，最后用UNION ALL合并结果。

解决方案SQL #

SELECT 
    t.id,
    kv.key AS attributtename,
    kv.value
FROM mytable t
CROSS JOIN jsonb_each_text(t.data) kv
WHERE kv.key NOT IN ('_validation_status') -- 排除不需要展开的嵌套对象字段
UNION ALL
SELECT 
    id,
    'label' AS attributtename,
    data->'_validation_status'->>'label' AS value
FROM mytable
ORDER BY id, attributtename;

说明 #

1. 第一部分查询：使用jsonb_each_text将data字段的顶级键值对展开，同时排除_validation_status这个嵌套对象字段，避免把整个对象作为值返回。
2. 第二部分查询：直接提取_validation_status中的label值，指定attributtename为label。
3. 合并结果：通过UNION ALL将两部分数据合并，最后按id和attributtename排序，得到目标格式。

执行结果 #

执行上述SQL后，将得到如下符合需求的结果：

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