如何从jsonb列提取元素 展平为键值对行并支持按键筛选

你可以直接使用PostgreSQL内置的jsonb_each()函数拆解jsonb对象的顶层键值对，直接拿到目标字段，不需要额外写复杂递归逻辑。

基础展平查询（无过滤） #

直接执行以下SQL即可把每个检测项拆成独立行：

SELECT
    t."Id" AS id,
    kv.key AS key_value,
    (kv.value ->> 'value') AS value
FROM 你的业务表名 t,
     jsonb_each(t."Result") AS kv;

说明：jsonb_each会把传入的jsonb对象拆成「键、值」形式的多行结果集，其中key就是检测项名称，value是对应检测项的嵌套jsonb对象，用->>操作符可直接提取嵌套对象里的value属性值。

带指定检测项过滤的查询 #

如果要按key_value指定值（比如示例里的'Green'）过滤，直接加WHERE条件即可：

SELECT
    t."Id" AS id,
    kv.key AS key_value,
    (kv.value ->> 'value') AS value
FROM 你的业务表名 t,
     jsonb_each(t."Result") AS kv
WHERE kv.key = 'Green'; -- 这里替换成你要过滤的检测项名称即可

样例数据验证 #

假设原始表数据如下：

执行带kv.key = 'Green'过滤的查询后，输出结果如下：

注意事项 #

• 如果你的Result字段里存在数组类型的顶层值，要先把数组元素也拆出来的话，可以搭配jsonb_typeof()先判断值类型，数组类型用jsonb_array_elements()二次展开即可，常规检测项为键值对对象的场景上面的SQL完全够用
• 提取value属性的时候如果存的是数值、布尔类型，要做后续逻辑计算可以把->>换成->再转对应数据类型，直接输出字符串内容用->>即可

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