PostgreSQL：带过滤条件拆分JSONB数组至多列

假设你的表名为test_table，包含一个jsonb类型的列data，存储结构如你提供的数组对象。以下是实现需求的SQL方案：

步骤拆解 #

• 将JSONB数组拆分为单行的对象元素
• 过滤出fieldUsedToFilter值为A或B的元素
• 通过条件聚合将对应value映射到A、B列

完整SQL语句 #

SELECT
    id, -- 假设表有主键id用于标识原行记录
    MAX(CASE WHEN elem->>'fieldUsedToFilter' = 'A' THEN elem->>'value' END) AS "A",
    MAX(CASE WHEN elem->>'fieldUsedToFilter' = 'B' THEN elem->>'value' END) AS "B"
FROM test_table,
     jsonb_array_elements(data) AS elem
WHERE elem->>'fieldUsedToFilter' IN ('A', 'B')
GROUP BY id;

关键部分说明 #

• jsonb_array_elements(data)：把data列的JSONB数组拆分成多行，每行对应一个数组元素对象，用别名elem指代
• elem->>'fieldUsedToFilter'：提取elem对象中fieldUsedToFilter字段的文本值，用于过滤和分支判断
• MAX(CASE ...)：通过条件聚合，将同一原行中符合条件的value分别映射到A、B列；因为每个原行中每个fieldUsedToFilter值只会出现一次（假设数据无重复），用SUM或MIN也能达到相同效果

测试示例 #

插入测试数据：

INSERT INTO test_table(id, data) VALUES
(1, '[
  {"value": "1", "fieldUsedToFilter": "A"}, 
  {"value": "5", "fieldUsedToFilter": "C"}, 
  {"value": "3", "fieldUsedToFilter": "B"}
]'::jsonb);

执行查询后得到结果：

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