PostgreSQL 按相同model值合并多行生成指定结构JSON的实现及问题解决
同模型新旧状态记录合并生成指定JSON数组的实现方案
原SQL报错及逻辑缺陷排查
你之前写的语句主要存在三类可直接导致执行失败的问题:
- PostgreSQL 中等值判断使用单等号
=,双等号==是前端语言语法,数据库解析时会直接抛出语法错误 json_build_object要求入参必须是「键、值、键、值」交替的成对格式,原写法中把CASE判断的返回值和版本字段混在参数列表里,没有形成合法的键值对结构,函数调用本身不合法- 没有按
model做分组聚合:直接逐行构造JSON会把每一条原始记录生成单独的JSON对象,根本无法实现同一个model下old、new两行记录合并为一个对象的效果
可落地的实现代码
整体实现分为两层聚合逻辑:
- 内层对业务筛选后的结果按
model分组,用条件判断把对应状态的版本值提取成独立字段 - 外层对分组后的结果构造标准结构的JSON对象,再聚合为要求的JSON数组
SELECT json_agg( json_build_object( 'model', model, 'old_version', old_version, 'new_version', new_version ) ) AS output_json FROM ( SELECT model, -- 同分组下匹配到的状态对应版本取出来即可,MAX/ANY_VALUE都可实现 MAX(CASE WHEN status = 'old' THEN version END) AS old_version, MAX(CASE WHEN status = 'new' THEN version END) AS new_version FROM ( -- 替换为你的业务筛选子查询 -- 例: SELECT model, version, status FROM business_table WHERE create_time > '2024-01-01' ) biz_t GROUP BY model ) grouped_t;
实现细节注意
- 条件聚合中提取版本值时,只要能取出同分组下唯一匹配的版本值即可,函数选择不影响最终结果,符合业务逻辑的前提下同一model下每个status只会对应1条有效记录
- 如果需要过滤掉只有old或只有new状态的不完整模型记录,可以在子查询
grouped_t层增加条件:WHERE old_version IS NOT NULL AND new_version IS NOT NULL - 如果版本值是数值类型,需要和示例输出的字符串格式对齐时,可以在
CASE返回值处增加::text做类型转换
内容的提问来源于stack exchange,提问作者code-geek
相关产品推荐
相关产品推荐

