如何在 JSON_AGG 中使用 ORDER BY 和 LIMIT

我有一個返回所需輸出的查詢。

SELECT 
   shop,
   JSON_AGG(item_history.* ORDER BY created_date DESC) as data
FROM item_history
GROUP BY
   shop;

結果：

[
 {
   "shop": "shop1",
   "data": [
     {
       "id": 226,
       "price": "0",
       "shop": "shop1.com",
       "country": "UK",
       "item": "item1",
       "created_date": "2021-06-07T08:48:42.338201",
     },
     {
       "id": 224,
       "price": "0",
       "shop": "shop1.com",
       "country": "UK",
       "item": "item 1",
       "created_date": "2021-06-07T07:53:25.030621",
     },
     ...
 },
 {
   "shop": "shop2",
   "data": [
     {
       "id": 225,
       "price": "0",
       "shop": "shop2.com",
       "country": "DE",
       "item": "Item 2",
       "created_date": "2021-06-07T08:48:36.443849",
     },
     ...
]

這正是我想要的輸出，但問題是它獲取數組下的所有項目data，限制該數組會很好。我嘗試添加LIMIT

SELECT 
   shop,
   JSON_AGG(item_history.* ORDER BY created_date DESC LIMIT 5) as data
FROM item_history
GROUP BY
   shop;

但我明白了syntax error at or near "limit"。是否可以限制輸出JSON_AGG？

ORDER BY裡面的語法JSON_AGG不接受LIMIT選項。我不知道以這種方式限制任何聚合函式的範圍的方法。作為替代方案，您可以嘗試以下ROW_NUMBER+FILTER方法：

• shop在分區內分配行號
• 在條件聚合中使用行號。

在 SQL 中，它看起來像這樣：

SELECT 
   shop,
   JSON_AGG(derived.* ORDER BY created_date DESC) FILTER (WHERE rn <= 5) AS data
FROM
   (
       SELECT
           *,
           ROW_NUMBER() OVER (PARTITION BY shop ORDER BY created_date DESC) AS rn
       FROM
           item_history
   ) AS derived
GROUP BY
   shop;

或者您可以使用 CTE 而不是派生表：

WITH
   cte AS
   (
       SELECT
           *,
           ROW_NUMBER() OVER (PARTITION BY shop ORDER BY created_date DESC) AS rn
       FROM
           item_history
   )
SELECT 
   shop,
   JSON_AGG(cte.* ORDER BY created_date DESC) FILTER (WHERE rn <= 5) AS data
FROM
   cte
GROUP BY
   shop;

結果將是相同的。差異（如果有的話）僅在於效率，因為 PostgreSQL 中的 CTE 可能會實現，而派生表通常不會。您應該自己測試一下，看看哪個選項更適合您。

注意：在這兩種情況下，聚合函式仍然具有相同的ORDER BY，但是您現在可以隨意更改它，或者完全刪除它：該FILTER子句確保您只有最後五個條目shop。

引用自：https://dba.stackexchange.com/questions/293868