PostgreSQL如何转换Clickhouse FDW返回字符串为数组/jsonb或避免格式转换

导致格式转换的核心原因是clickhouse_fdw会自动识别传入文本中类似PostgreSQL数组的[]结构，自动转换为Clickhouse兼容的{}数组文本格式，可通过以下方法规避：

• 写入前给json字符串增加自定义前后缀，避免FDW识别为数组结构。写入示例：

  -- 前后缀可自定义为任意不会出现在json内容里的字符串
  'JSON:' || jsonb_agg(jsonb_build_object('keyA', valueA, 'keyB' ,valueB))::text
  

  查询时移除前缀即可拿到原始json字符串。
• 将json字符串做base64编码后写入，完全规避特殊字符转换。写入示例：

  encode(jsonb_agg(jsonb_build_object('keyA', valueA, 'keyB' ,valueB))::text::bytea, 'base64')
  

  查询时用decode(your_field, 'base64')::text即可还原原始json。
• 创建Clickhouse外部表时，给对应文本字段增加raw格式配置，强制FDW不做自动转换。示例外部表字段配置：

  create foreign table ch_table (
    -- 其他字段省略
    json_str text option (format 'raw')
  ) server clickhouse_server options (table 'your_ch_table');
  

  不同版本clickhouse_fdw的参数名可能略有差异，可对应调整。

如果已经有存量数据无法修改写入逻辑，直接通过字符串替换即可完成格式转换，示例SQL：

SELECT
  -- 依次替换单引号为双引号、外层{{为[、外层}}为]，再转jsonb
  replace(
    replace(
      replace(your_json_field, '''', '"'),
      '{{', '['
    ),
    '}}', ']'
  )::jsonb AS converted_json
FROM your_ch_fdw_table;

如果数组中包含多个元素，该替换逻辑同样适配，转换后的格式完全符合jsonb的解析要求。

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