CODEWITHSUNDEEP
mysqlsqlaggregate-functions
David542
David542

Reputation: 110083

mysql JSON_ARRAYAGG or GROUP_CONCAT with limit

Is there a way in mysql8.0 to do a limit within JSON_ARRAYAGG or GROUP_CONCAT? For example:

WITH season AS (
    select 'DAL'  as team, 4 as wins, 2000 as season UNION
    select 'DAL'  as team, 10 as wins, 2001 as season UNION
    select 'DAL'  as team, 9 as wins, 2002 as season UNION
    select 'GB'  as team, 2 as wins, 2000 as season UNION
    select 'GB'  as team, 3 as wins, 2001 as season UNION
    select 'GB'  as team, 4 as wins, 2002 as season
) SELECT
    team,
    GROUP_CONCAT(wins order by season desc separator '+') wins_str,
    JSON_ARRAYAGG(wins) wins_arr
FROM season
GROUP BY team;

For example, how would I only get the first 5 wins in the above two fields? For example I'm looking to do something like you can do within BigQuery STRING_AGG, where it accepts a LIMIT.

Upvotes: 0

Views: 1044

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

The simplest method is window functions:

SELECT
    team,
    GROUP_CONCAT(wins ORDER BY season DESC SEPARATOR '+') wins,
    JSON_ARRAYAGG(wins)
FROM (
    SELECT s.*,
    ROW_NUMBER() OVER (
        PARTITION BY team ORDER BY seasons DESC
    ) AS seqnum
    FROM seasons s
) s
WHERE seqnum <= 5
GROUP BY team

Upvotes: 2

Related Questions