PostgreSQL中使用row_to_json时如何保留原列名生成JSON

你遇到的问题是因为ROW()构造的是匿名记录，PostgreSQL无法识别原列名，所以生成的JSON会用f1、f2这类默认键名。以下两种方法可以在不创建新类型的前提下解决这个问题：

方法1：子查询构造带列名的行 #

通过子查询包装需要转为JSON的列，PostgreSQL会保留子查询中的列名：

CREATE TABLE mod(id text, others text);

INSERT INTO mod
SELECT 
  policyholder_id, 
  to_json((SELECT t FROM (SELECT fname, lname, risk_score, high_risk) t))::text
FROM policyholder;

方法2：显式指定键值对（json_build_object） #

使用json_build_object函数手动映射列名和值，完全可控：

CREATE TABLE mod(id text, others text);

INSERT INTO mod
SELECT 
  policyholder_id, 
  json_build_object(
    'fname', fname,
    'lname', lname,
    'risk_score', risk_score,
    'high_risk', high_risk
  )::text
FROM policyholder;

效果验证 #

执行任意一种方法后，mod表的others列都会生成符合需求的JSON：

{"fname": "ADAM", "lname": "OCHSENBEIN", "risk_score": "88", "high_risk": "1"}

方法对比 #

• 子查询方式：适合列较多的场景，无需逐个列名重复编写，维护更方便
• json_build_object方式：支持自定义键名（如果需要修改JSON中的键名），性能略优

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