PostgreSQL转jsonb出现转义字符及多行结果问题

1. 解决to_jsonb产生大量转义字符的问题 #

你之前用字符串拼接生成的是已经符合JSON格式的字符串，直接调用to_jsonb()会把整个字符串当作JSON的「字符串类型值」处理，自然会对内部的引号、特殊字符进行转义。

正确的做法是把拼接好的字符串直接转换为jsonb类型，而非用to_jsonb()包装：

• 方式一：使用类型转换

  -- 替换原来的to_jsonb(拼接字符串)
  CAST('{"key": "value", "foo": "bar"}' AS jsonb)
  

• 方式二：使用jsonb()构造函数（效果和CAST一致）

  jsonb('{"key": "value", "foo": "bar"}')
  

如果你的拼接逻辑是动态生成的（比如从其他字段拼接），直接把拼接结果CAST成jsonb即可，不需要经过to_jsonb处理。

2. 解决jsonb_build_object返回多行而非一行的问题 #

出现4行结果，大概率是你没有对多行数据做聚合处理，而是每行数据单独调用了jsonb_build_object()，导致每行输出一个JSON对象。

场景1：将多行键值对合并为单个JSON对象 #

如果需要把多行的key-value组合成一个JSON对象（比如把表中id和name字段合并成{"1": "张三", "2": "李四"}），用jsonb_object_agg()聚合：

SELECT jsonb_object_agg(id::text, name) 
FROM your_table 
WHERE your_condition;

场景2：将多行数据合并为一个JSON数组 #

如果需要把每行的JSON对象组合成一个数组（比如[{"id":1,"name":"张三"}, {"id":2,"name":"李四"}]），用jsonb_agg()：

SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) 
FROM your_table 
WHERE your_condition;

场景3：单条数据构造JSON却返回多行 #

如果是单条数据却返回多行，检查查询是否有多余的JOIN或者未添加过滤条件，导致返回了多条记录，每条记录生成一个JSON对象。

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