PostgreSQL中将表数据转换为列名作为键的JSON数组的实现方法

以下几种方案都可以实现你要的效果：

• 方案1：通用快捷方案，直接用内置函数组合，执行后直接返回目标JSON数组

  SELECT json_agg(row_to_json(list)) FROM list;
  

  原理：row_to_json(list)会逐行将表的行数据转换为以字段名为键、对应值为值的JSON对象，json_agg函数会把所有单行JSON对象聚合为一个完整的JSON数组。
• 方案2：格式化输出方案，如果你需要输出带缩进、换行的易读格式，和你给出的示例格式完全一致，可以用这个

  SELECT jsonb_pretty(json_agg(row_to_json(list))::jsonb) FROM list;
  

• 方案3：自定义字段方案，如果你后续需要筛选输出字段、修改JSON键名或者对字段值做预处理，用这个可控性更高

  SELECT json_agg(
      json_build_object(
          'firstname', firstname,
          'lastname', lastname,
          'age', age
      )
  ) FROM list;
  

  你可以按需调整json_build_object里的键值对，比如只保留firstname和age两个字段，或者把age字段的键名改成user_age都可以直接在这里修改。
• 方案4：直接导出为JSON文件方案，如果你需要把结果直接保存为本地JSON文件，可以在psql命令行工具中执行以下语句

  -- 先设置输出格式
  \pset format unaligned
  \pset tuples_only on
  -- 指定输出文件路径
  \o ./list_result.json
  -- 执行查询
  SELECT json_agg(row_to_json(list)) FROM list;
  -- 关闭文件输出
  \o
  

  执行完成后你就能在指定路径下看到符合要求的JSON文件。

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