PostgreSQL中如何将表字段转换为指定结构的JSON数据

第一种格式（classification为数组结构）实现 #

直接用内置函数即可完成，不需要额外扩展。
注意示例数据中classification字段的逗号后带空格，拆分时需要先处理空白字符，避免数组元素带多余空格，参考SQL如下：

SELECT
  json_build_object(
    'msg', message,
    'sentiment', sentiment,
    'classification', string_to_array(trim(regexp_replace(classification, '\s*,\s*', ',', 'g')), ',')
  ) AS result_json
FROM 你的实际表名;

执行后输出的是标准合法JSON，和预期格式一致：

{"msg":"any text", "sentiment":"positive","classification":["mobile","communication"]}

注：提问给出的示例JSON中"mobile,位置多打了一个双引号，属于书写笔误，上述SQL输出的是符合JSON规范的结果。

第二种格式（classification拆分为独立键值对）实现 #

该格式同样可以实现，核心思路是先把逗号分隔的分类值拆分为独立行，生成带序号的键名后再聚合为JSON对象，参考SQL如下：

WITH split_data AS (
  SELECT
    ctid,
    message,
    sentiment,
    concat('classification ', row_number() OVER (PARTITION BY ctid)) AS cls_key,
    trim(cls_item) AS cls_val
  FROM 你的实际表名,
       unnest(string_to_array(classification, ',')) AS cls_item
  WHERE trim(cls_item) <> '' -- 过滤拆分后为空的无效项
)
SELECT
  json_build_object('msg', message, 'sentiment', sentiment) 
  || json_object_agg(cls_key, cls_val) AS result_json
FROM split_data
GROUP BY ctid, message, sentiment;

执行后输出的标准JSON结果如下：

{"msg":"any text", "sentiment":"positive","classification 1":"mobile","classification 2":"communication"}

注：提问给出的示例中"classification 2"后缺失冒号，属于书写笔误，上述SQL输出为符合规范的合法JSON。

补充说明 #

如果classification字段存在多分类数量不固定的情况，上述两种方案都可以自动适配，不需要提前固定分类数量。如果需要对拆分后的分类值做去重，在unnest拆分后加distinct处理即可。

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