PostgreSQL如何按s_position排序聚合行且保持结果行数不变？

原查询语句：

SELECT p.*, po.product_options
    FROM product p
    LEFT JOIN  (
        SELECT po.p_id, 
               json_agg(
                  json_build_object('id', po.id, 'name', po.name, 'options', pom.option_name)) AS product_options
        FROM product_options po
        INNER JOIN (
          SELECT product_options_id, json_agg(option_name) as option_name
          FROM product_options_value
          GROUP BY product_options_id
          ) pom ON pom.product_options_id = po.id
        GROUP BY p_id
    ) po ON po.p_id = p.id
WHERE p.id = 1;

尝试对product_options表的行按s_position列排序时，出现报错：

ERROR: column "po.s_position" must appear in the GROUP BY clause or be used in an aggregate function

若将s_position加入GROUP BY子句，会得到2行结果，不符合预期的1行结果。

解决方案 #

PostgreSQL的json_agg聚合函数支持在内部指定排序规则，无需将s_position加入GROUP BY子句。只需在json_agg的参数后添加ORDER BY po.s_position，即可实现按s_position排序聚合行，同时保持原有分组逻辑（结果行数不变）。

修改后的查询语句：

SELECT p.*, po.product_options
    FROM product p
    LEFT JOIN  (
        SELECT po.p_id, 
               json_agg(
                  json_build_object('id', po.id, 'name', po.name, 'options', pom.option_name)
                  ORDER BY po.s_position) AS product_options -- 此处添加排序规则
        FROM product_options po
        INNER JOIN (
          SELECT product_options_id, json_agg(option_name) as option_name
          FROM product_options_value
          GROUP BY product_options_id
          ) pom ON pom.product_options_id = po.id
        GROUP BY p_id
    ) po ON po.p_id = p.id
WHERE p.id = 1;

原理说明 #

• json_agg允许在聚合过程中对输入的行进行排序，排序字段不需要出现在GROUP BY中，因为它是聚合操作内部的排序逻辑，不会改变分组的依据（依然按p_id分组）。
• 这样既满足了按s_position排序聚合行的需求，又保证每个p_id只返回一行聚合结果。

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