Search code examples
sqljsonpostgresqlaggregate-functions

How can I aggregate rows in order?

I have this query:

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;

Here is a db fiddle:
http://sqlfiddle.com/#!17/b47e3b/8

Now I want to order rows from the table product_options by the column s_position. I get:

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

But if I add the column to GROUP BY, I get 2 rows instead of just one. How can I order by s_position at the table product_options so that I still get the same result, but with ordered options?

Solution

  • After aggregating all rows from product_options per p_id in the subquery, trying to order by product_options.s_position would be ambiguous. Hence the error message you saw.

    To aggregate rows from product_options in the order set by s_position:

    SELECT *
FROM   product p
LEFT  JOIN (
   SELECT po.p_id AS id
        , json_agg(json_build_object('id', id, 'name', name, 'options', pom.option_name)
                   ORDER BY po.s_position) AS product_options  -- !
   FROM   product_options po
   JOIN  (
      SELECT product_options_id AS id, json_agg(option_name) AS option_name
      FROM   product_options_value
      GROUP  BY 1
      ) pom USING (id)
   GROUP  BY 1
   ) po USING (id)
WHERE  p.id = 1;

    fiddle

    Alternatively, you might order rows in an additional subquery, which is typically faster. See: