CODEWITHSUNDEEP
mysql
Srinivas Lakshman
Srinivas Lakshman

Reputation: 489

Why LIMIT Clause failing for JSON_ARRAYAGG function?

The limit clause seems to be not working when JSON_ARRAYAGG function is used.

Is there any better way to achieve the functionality ? 

SELECT * FROM USER_TABLE ORDER BY RAND() LIMIT 2;
--> Gives me 2 random employee details - which is perfect.

SELECT JSON_ARRAYAGG(JSON_OBJECT('userId', user_id)) FROM USER_TABLE ORDER BY RAND() LIMIT 2;
--> Gives me ALL the employee details. - which is INCORRECT.

Upvotes: 2

Views: 931

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Using JSON_ARRAYAGG() without a GROUP BY clause you will get only one row. Only then is LIMIT 2 applied, and (of course) doesn't have any effect on that single row. What you probably need is a LIMIT 2 subquery in the FROM clause:

SELECT JSON_ARRAYAGG(json_obj)
FROM (
    SELECT JSON_OBJECT('userId', user_id) as json_obj
    FROM USER_TABLE
    ORDER BY RAND()
    LIMIT 2
) x

or 

SELECT JSON_ARRAYAGG(JSON_OBJECT('userId', user_id))
FROM (
    SELECT user_id
    FROM USER_TABLE
    ORDER BY RAND()
    LIMIT 2
) x

Upvotes: 5

Related Questions