PostgreSQL中如何将jsonb字段的productname和dateOfSale拆分至不同列展示

问题原因 #

你原来的写法存在两个问题：

1. 筛选条件的引号用法错误，把两个判断逻辑包成了单个字符串，筛选不会生效
2. 逻辑上将两个字段的取值都输出到同一列的不同行，没有做行转列处理，自然无法拆成两列展示

正确查询语句 #

根据你提供的JSON结构（每个数组元素为{"name":"xxx","value":"xxx"}的键值对，每两个元素对应一条销售记录），使用以下查询即可得到预期结果：

SELECT
    MAX(CASE WHEN elem.content ->> 'name' = '$.productname' THEN elem.content ->> 'value' END) AS productname,
    MAX(CASE WHEN elem.content ->> 'name' = '$.dateofSale' THEN elem.content ->> 'value' END) AS dateofsale
FROM stores,
     jsonb_array_elements(result) WITH ORDINALITY AS elem(content, idx)
WHERE store_id = '1'
GROUP BY stores.id, (elem.idx - 1) / 2
ORDER BY (elem.idx - 1) / 2

如果你的result数组每个元素就是完整的销售对象，直接包含productname和dateOfSale两个键，可使用更简单的写法：

SELECT
    elem ->> 'productname' AS productname,
    elem ->> 'dateOfSale' AS dateofsale
FROM stores,
     jsonb_array_elements(result) elem
WHERE store_id = '1'

实现逻辑 #

先用jsonb_array_elements函数将result列的JSON数组拆分为单个元素，再通过条件聚合将不同name对应的value映射到不同的列，最后按组聚合即可得到每行对应一条销售记录的结果。

内容的提问来源于stack exchange，提问作者Xux.rd