If you’re getting an error that reads “JSON aggregates do not support order-by within group when specified with grouping sets, cubes and rollups. Try your query without order-by within group.” it looks like you’re trying to use the ORDER BY clause inside a JSON aggregate function when using GROUPING SETS, CUBE, or ROLLUP.
To fix this error you’ll need to remove ORDER BY from the aggregate function when using those clauses.
Example of Error
Here’s an example of code that produces the error:
SELECT
OrderID,
Category,
SUM(Quantity * UnitPrice) AS Subtotal,
JSON_ARRAYAGG(ProductName ORDER BY ProductName) AS Products
FROM OrderItems
GROUP BY GROUPING SETS((OrderID), (Category), (OrderID, Category), ());Output:
Msg 13667, Level 16, State 1, Line 1
JSON aggregates do not support order-by within group when specified with grouping sets, cubes and rollups. Try your query without order-by within group.
In this case I put an ORDER BY clause in the JSON_ARRAYAGG() function while also specifying GROUP BY GROUPING SETS. As mentioned, we can’t use the ORDER BY clause when using GROUP BY GROUPING SETS or CUBE or ROLLUP.
Solution
The most obvious solution is to remove the ORDER BY clause:
SELECT
OrderID,
Category,
SUM(Quantity * UnitPrice) AS Subtotal,
JSON_ARRAYAGG(ProductName) AS Products
FROM OrderItems
GROUP BY GROUPING SETS((OrderID), (Category), (OrderID, Category), ());Result:
OrderID Category Subtotal Products
------- ----------- -------- -----------------------------------------------------------------------------------------------------------------------------------------
1 Accessories 29.99 ["Monitor Stand"]
2 Accessories 19.99 ["Mouse Pad XL"]
3 Accessories 94.94 ["Laptop Sleeve","HDMI Cable"]
null Accessories 144.92 ["Monitor Stand","Mouse Pad XL","Laptop Sleeve","HDMI Cable"]
1 Peripherals 119.97 ["Wireless Keyboard","USB-C Hub"]
2 Peripherals 164.98 ["Mechanical Keyboard","Webcam HD"]
3 Peripherals 27.99 ["USB Hub"]
null Peripherals 312.94 ["Wireless Keyboard","USB-C Hub","Mechanical Keyboard","Webcam HD","USB Hub"]
null null 457.86 ["Monitor Stand","Mouse Pad XL","Laptop Sleeve","HDMI Cable","Wireless Keyboard","USB-C Hub","Mechanical Keyboard","Webcam HD","USB Hub"]
1 null 149.96 ["Wireless Keyboard","USB-C Hub","Monitor Stand"]
2 null 184.97 ["Mechanical Keyboard","Mouse Pad XL","Webcam HD"]
3 null 122.93 ["Laptop Sleeve","HDMI Cable","USB Hub"]
This time my results were returned without error. That said, the results of the aggregate function weren’t ordered, so I would need to sort the results after the fact in the application layer.
Of course, that is not to say that we can never use the ORDER BY clause in such functions. It’s only an issue if we use GROUP BY GROUPING SETS or CUBE or ROLLUP. If we don’t use those, then we can order the output all we want:
SELECT
OrderID,
JSON_ARRAYAGG(ProductName ORDER BY UnitPrice DESC) AS ProductsByPrice
FROM OrderItems
GROUP BY OrderID;Output:
OrderID ProductsByPrice
------- --------------------------------------------------
1 ["Wireless Keyboard","USB-C Hub","Monitor Stand"]
2 ["Mechanical Keyboard","Webcam HD","Mouse Pad XL"]
3 ["USB Hub","Laptop Sleeve","HDMI Cable"]