PostgreSQL 14.9中拆分JSONB数组对象为单独行
2026-7-11
PostgreSQL 14.9：展开JSONB数组并筛选指定元素
原表结构与数据
| Name (Txt) | Detail (JSONB) | state (JSONB) |
|---|---|---|
| apple | [{"code": "156", "color": "red"}, {"code": "156", "color": "blue"}] | [{"ap": "good", "op2": "bad"}] |
| orange | [{"code": "156", "color": "red"}, {"code": "235", "color": "blue"}] | [{"op": "bad", "op2": "best"}] |
| lemon | [{"code": "156", "color": "red"}, {"code": "156", "color": "blue"}] | [{"cp": "best", "op2": "good"}] |
期望输出
| Name (Txt) | Detail (JSONB) | state (JSONB) |
|---|---|---|
| apple | {"code": "156", "color": "red"} | {"ap": "good", "op2": "bad"} |
| apple | {"code": "156", "color": "blue"} | {"ap": "good", "op2": "bad"} |
| orange | {"code": "156", "color": "red"} | {"op": "bad", "op2": "best"} |
| lemon | {"code": "156", "color": "red"} | {"cp": "best", "op2": "good"} |
| lemon | {"code": "156", "color": "blue"} | {"cp": "best", "op2": "good"} |
解决方案
使用jsonb_array_elements展开JSONB数组，同时筛选指定code值的元素，即可得到目标结果。修正后的SQL语句如下：
SELECT "Name (Txt)" , jsonb_build_object('code', elem->>'code', 'color', elem->>'color') AS "Detail (JSONB)" , state::JSONB FROM your_table, jsonb_array_elements("Detail (JSONB)") AS elem WHERE elem->>'code' = '156';
关键逻辑说明
jsonb_array_elements("Detail (JSONB)") AS elem：将原字段中的JSONB数组拆分为单行JSON对象，每个对象对应数组中的一个元素。WHERE elem->>'code' = '156'：过滤出数组中code字段值为'156'的元素，排除不符合条件的条目。jsonb_build_object：重新构造符合输出格式要求的JSONB对象，保证结构与期望一致。
内容的提问来源于stack exchange，提问作者Makoto Makoto
相关产品推荐
相关产品推荐
