问题
select patient_id,concat('{"itemText":"',item_text,'","itemValue":"',item_value,'"}') from hs_patient_groups where active = 1;eef41128c47c401abb7f8885a5f9fbdf {"itemText":"旧","itemValue":"2"}
eef41128c47c401abb7f8885a5f9fbdf {"itemText":"新","itemValue":"1"}
0564ccc4c07b4df98bf8207f7084b493 {"itemText":"患者1组","itemValue":"patientGroup1"}
e92acabeb871422dacd11199df89bbc3 {"itemText":"患者1","itemValue":"患者1"}
af2b9bb388af484fb99c9d03cc2041ca {"itemText":"新","itemValue":"1"}
5e4ff36bebd7478892978f875f48f515 {"itemText":"患者1","itemValue":"患者1"}
344035419b3a4c4e8b29d76c627679f9 {"itemText":"患者2","itemValue":"患者2"}
bc266205ca604150b3d800aab059fba8 {"itemText":"患者2","itemValue":"患者2"}
1801a4524b084d2b8197bfcc33849b24 {"itemText":"患者1","itemValue":"患者1"}
1801a4524b084d2b8197bfcc33849b24 {"itemText":"患者2","itemValue":"患者2"}
73cd39179ab147e6968858aecd56f214 {"itemText":"冠心病组","itemValue":"02"}
d54ced0ccbba4bb2b1d3359877fb0620 {"itemText":"瓣膜组","itemValue":"01"}
3c92057be370414b822407b1ca89ad1b {"itemText":"瓣膜组","itemValue":"01"}
591883a377d6428f985bdb99b762929b {"itemText":"瓣膜组","itemValue":"01"}
2b1b8ac08c5d4ac39625c20bd206ed00 {"itemText":"患者2","itemValue":"患者2"}
19822b6d38504d1a8af94dbfa32c2c0e {"itemText":"患者2","itemValue":"患者2"}
ab62ee95526e405e93f117a25db77eee {"itemText":"瓣膜组","itemValue":"01"}
181e27e744e242719a47d5a86260eeb1 {"itemText":"冠心病组","itemValue":"02"}
e1d862aa6baa474987545776ad8e84db {"itemText":"冠心病组","itemValue":"02"}如何将相同patient_id，不同concat的值合并为一行
合并相同 patient_id 的 JSON 数据为数组
要将相同 patient_id 的不同 JSON 数据合并为一行（合并为一个 JSON 数组），你可以使用 PostgreSQL 的 json_agg 或 jsonb_agg 聚合函数。
解决方案（推荐）
SELECT patient_id,json_agg(json_build_object('itemText', item_text, 'itemValue', item_value)) AS items
FROM hs_patient_groups
WHERE active = 1
GROUP BY patient_id;
结果示例
执行上述查询后，结果将类似于：
patient_id | items
-------------------------------------+--------------------------------------------------------------------
eef41128c47c401abb7f8885a5f9fbdf | [{"itemText":"旧","itemValue":"2"}, {"itemText":"新","itemValue":"1"}]
0564ccc4c07b4df98bf8207f7084b493 | [{"itemText":"患者1组","itemValue":"patientGroup1"}]
1801a4524b084d2b8197bfcc33849b24 | [{"itemText":"患者1","itemValue":"患者1"}, {"itemText":"患者2","itemValue":"患者2"}]
... | ...
其他选项
- 如果你需要保持原始查询中的 JSON 字符串格式：
SELECT patient_id,json_agg(concat('{"itemText":"',item_text,'","itemValue":"',item_value,'"}')::json) AS items
FROM hs_patient_groups
WHERE active = 1
GROUP BY patient_id;
- 如果你使用的是 PostgreSQL 9.4 或更早版本（没有
json_agg）：
SELECT patient_id,'[' || string_agg(concat('{"itemText":"',item_text,'","itemValue":"',item_value,'"}'), ',') || ']' AS items
FROM hs_patient_groups
WHERE active = 1
GROUP BY patient_id;
注意：最后一个方法生成的是 JSON 字符串而不是真正的 JSON 类型。
