PostgreSQL json_agg，并尝试分组依据和排序依据(列必须出现在 GROUP BY 中)

Question

我已经阅读了几个关于这个错误的 stackoverflow，但我还是不明白。

-- Returns full JSON document of a user, and all their user_files.
with user_files_json as (
    select user_id,
           json_agg(
             json_build_object(
               'user_file_id', user_file_id,
               's3_key', s3_key,
               'settings', series_settings
             )
           ) as user_files
      from user_files
     where user_id = :user_id
  group by user_id
  order by user_file_id asc
)
select json_build_object(
         'user_id', user_id,
         'user_name', user_name,
         'job_type_name', job_type_name,
         'user_files', user_files
       )
  from user_files_json
  join users using(user_id)
  join job_type using(job_type_id);

我希望文件按其 user_file_id 顺序列出。就这样。如果我取出 order by 一切正常，但文件以错误的顺序出现。

但我收到一条错误消息:

column "user_files.user_file_id" must appear in the GROUP BY clause or be used in an aggregate function

作为一个人，我告诉 postgresql 的内容对我来说非常有意义，但是有人可以向我解释我做错了什么以及如何解决它吗？

Answer 1

因此您希望按 user_file_id 对 JSON 对象进行排序。您必须首先创建 JSON 对象，然后对它们进行分组和聚合，因为 PostgreSQL 仅返回聚合和 user_id 并且不能根据聚合中的内容对它们进行排序，只能根据聚合中的内容进行排序结果集。

select user_id,
       json_agg(obj) as user_files
  from (
    select user_id, json_build_object(
           'user_file_id', user_file_id,
           's3_key', s3_key,
           'settings', series_settings
      order by user_file_id asc
         ) as obj
  from user_files
 where user_id = :user_id) tmp
group by user_id