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.
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
