如何将PL/pgSQL函数返回的多行结果转换为单个JSON数组？

嘿，我来帮你搞定这个问题～要把get_users_list()返回的多行数据转成单个JSON数组，PostgreSQL本身自带了非常实用的JSON处理函数，这里有几种接地气的方案：

方案1：不修改原函数，直接用JSON聚合函数包装调用 #

这是最省心的方式，完全不用改动已有的函数，只需要在调用时用json_agg()把返回的行聚合起来：

SELECT json_agg(t) FROM get_users_list() t;

• 原理：json_agg()会自动把每一行记录转换成一个包含所有字段的JSON对象，再把所有对象打包成一个JSON数组。
• 如果需要自定义JSON结构（比如重命名字段、组合姓名），可以用json_build_object()手动构造每个对象：

  SELECT json_agg(
    json_build_object(
      'user_id', t.id,
      'full_name', concat(t.name, ' ', t.surname),
      'role_id', t.fkrole,
      'username', t.username
    )
  ) FROM get_users_list() t;
  

方案2：修改原函数，让它直接返回JSON数组 #

如果你的业务场景经常需要这个JSON格式的结果，直接修改函数返回类型会更高效：

CREATE OR REPLACE FUNCTION get_users_list() RETURNS JSON as $$
BEGIN
  RETURN (
    SELECT json_agg(t) FROM (
      SELECT users.id, users.name, users.surname, users.fkrole, users.username 
      FROM users
    ) t
  );
END;
$$ LANGUAGE plpgsql;

之后直接调用SELECT get_users_list();就能拿到完整的JSON数组，不用再做额外处理。

方案3：用row_to_json()配合json_agg() #

和方案1逻辑类似，先通过row_to_json()把每行转成JSON对象，再聚合为数组：

SELECT json_agg(row_to_json(t)) FROM get_users_list() t;

这个方法和方案1效果差不多，row_to_json()可以显式控制行转JSON的细节（比如添加true参数保留字段名大小写，不过默认已经包含字段名了）。

小提示 #

• 如果返回结果里有NULL值，生成的JSON中会显示null，这是符合JSON规范的；
• 要是想要更紧凑的JSON格式（去掉多余空格），可以用jsonb_agg()替代json_agg()，再转成JSON：SELECT jsonb_agg(t)::JSON FROM get_users_list() t;

内容的提问来源于stack exchange，提问作者Matteo M.