3

I have a query which returns a desired output.

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

Results:

[
  {
    "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",
      },
      ...
]

Which is exactly the output I want but the issue is that it fetches all items under data array and it would be nice to limit that array. I tried adding LIMIT

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

But I get syntax error at or near "limit". Is it possible to limit the output from JSON_AGG?

1 Answer 1

6

The syntax of the ORDER BY inside JSON_AGG does not accept a LIMIT option. I am not aware of a way to limit the scope of any aggregate function in this way. As an alternative, you can try the following ROW_NUMBER + FILTER approach:

  • assign row numbers within shop partitions
  • use the row numbers in conditional aggregation.

In SQL it would look like this:

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;

Or you could use a CTE instead of a derived table:

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;

The results would be identical. The difference, if any, would only be in efficiency, because CTEs in PostgreSQL may be materialised while derived tables are typically not. You should test for yourself to see which option works better for you.

Note: In both cases the aggregate function still has the same ORDER BY, but you can now change it however you like, or remove it altogether: the FILTER clause makes sure you only have the last five entries per shop anyway.

2
  • Perfect, @AndriyM, I needed to do one adjustment for the queries to work to avoid ERROR: missing FROM-clause entry for table. For the 1st you would need to use derived.shop in SELECT and` GROUP BY` statements. Aggregate function should be JSON_AGG(derived ORDER BY ..... 2nd query had the same issue, replaced same fields with cte and it worked perfectly. Thanks!
    – lrai
    Jun 7, 2021 at 12:39
  • @lrai: You are right about the JSON_AGG argument, I didn't pay proper attention there, thanks, would have corrected it in my answer but @ypercubeᵀᴹ has already spared me the trouble (much appreciated). As for qualifying the shop column with derived. (or cte.), I don't believe it's necessary, the unqualified reference should be perfectly valid in both contexts.
    – Andriy M
    Jun 7, 2021 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.