Invalid group of use function using nested JSON_ARRAYAGG()

user_11930252 0
Viewed 529

I have the following simple query

select 
    jrt.threadId,
    JSON_ARRAYAGG(
        JSON_OBJECT(
        'roundId',jrr.roundId,
        'bets', JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'betId', bets.betId,
                        'amount', bets.amount
                    )
            )
        )
    ) as rounds
from threads jrt
    left join rounds jrr on jrt.threadId = jrr.threadId
            left join bets on jrr.roundId= bets.roundId
group by jrt.threadId

Which substantially works if I remove bets from the first JSON_OBJECT key-values.

I am having a rough time understanding the exact reason of the error, as it is little suggestive.

{"code":"ER_INVALID_GROUP_FUNC_USE","errno":1111}

The query also works if I use theJSON_ARRAY aggregate function on bets, instead of JSON_ARRAYAGG.

If Bets is an aggregate, why is JSON_ARRAYAGG throwing an error?

nested-queries mysql sql
1 Answers

Nested JSON_ARRAYAGG is not allowed instead you have to use nested query. I have modified your query following should work

select 
            jrt.threadId,
            JSON_ARRAYAGG(
                JSON_OBJECT(
                'roundId',jrr.roundId,
                'bets',( select  JSON_ARRAYAGG(
                            JSON_OBJECT(
                                'betId', bets.betId,
                                'amount', bets.amount
                            ) from threads jrt
            left join rounds jrr on jrt.threadId = jrr.threadId
                    left join bets on jrr.roundId= bets.roundId
                    ))
                )
            ) as rounds
        from threads jrt
            left join rounds jrr on jrt.threadId = jrr.threadId
                    left join bets on jrr.roundId= bets.roundId
        group by jrt.threadId

if some can optimize it further let me know

user_64526440
Related