PostgreSQL生成JSON字符串的方法汇总

-- 示例：用户表单行转 json
select row_to_json(users) from users where id = 1;

{"id":1,"name":"张三","age":25,"email":"zhangsan@test.com"}

-- 自定义键名，只返回需要的字段
select 
  json_build_object(
    'userid', id,
    'username', name,
    'userage', age
  ) as user_json
from users 
where id = 1;

{"userid":1,"username":"张三","userage":25}

-- 所有用户转 json 数组
select json_agg(users) from users;

-- 只筛选指定字段转数组
select json_agg(
  json_build_object('id',id,'name',name)
) from users where age > 20;

[{"id":1,"name":"张三"},{"id":2,"name":"李四"}]

select array_to_json(array[1,2,3,4]);  -- [1,2,3,4]
select array_to_json(array['苹果','香蕉']);  -- ["苹果","香蕉"]

select json_build_object(
  'code', 200,
  'msg', 'success',
  'data', json_build_object(
    'user', row_to_json(users),
    'createtime', now()
  )
) from users where id = 1;

{
  "code":200,
  "msg":"success",
  "data":{
    "user":{"id":1,"name":"张三","age":25},
    "createtime":"2025-05-20 10:00:00"
  }
}

select json_build_object(
  'total', 2,
  'list', json_agg(users)
) from users;

-- 输出标准 json 字符串
select row_to_json(users)::text from users where id = 1;