Why the LIMIT are ignored when SELECT using JSON_ARRAYAGG?

Question

set countt = PAGE_ * 5 - 5;

        set @data = (select JSON_ARRAYAGG(JSON_OBJECT('id', room_participants.id, 'isAdmin', room_participants.isAdmin, 'userId', room_participants.userId, 'joinTime', room_participants.joinTime, 'leftTime', room_participants.leftTime, 'status', room_participants.status, 'kickTime', room_participants.kickTime, 'displayName', users.displayName, 'phone', users.phone, 'created', created))
                        from room_participants 
                        LEFT JOIN users ON room_participants.userId = users.id 
                        WHERE room_participants.roomId = ROOMID_ LIMIT 5 OFFSET countt);

I need the above query to display only limit to 5 data in json_arrayagg format, but its return me the whole data without limit it to 5. What is the problem with my query?

Tim Biegeleisen · Accepted Answer · 2019-11-13 02:12:42Z

I don't know the exact reason why your code isn't working, but one workarond would be to apply LIMIT to a subquery, and then form the JSON based on that:

SET countt = PAGE_ * 5 - 5;

SET @data = (
    SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'isAdmin', isAdmin, 'userId', userId, 'joinTime', joinTime, 'leftTime', leftTime, 'status', status, 'kickTime', kickTime, 'displayName', displayName, 'phone', phone, 'created', created))
    FROM
    (
        SELECT rp.id, rp.isAdmin, rp.userId, rp.joinTime, rp.leftTime, rp.status,
               rp.kickTime, u.displayName, u.phone, created
        FROM room_participants rp
        LEFT JOIN users u ON rp.userId = u.id 
        WHERE rp.roomId = ROOMID_
        LIMIT 5 OFFSET countt
    ) t
);

Note that using LIMIT without ORDER BY is generally not a well-defined thing. You should also ideally add an ORDER BY clause to your subquery.

Ed Bangga · Accepted Answer · 2019-11-13 02:12:09Z

2

you need to use subquery first to limit your result before aggregating.

set countt = PAGE_ * 5 - 5;

set @data = (select JSON_ARRAYAGG(JSON_OBJECT('id', id, 'isAdmin'
                , isAdmin, 'userId', userId, 'joinTime'
                , joinTime, 'leftTime', leftTime
                , 'status', status, 'kickTime', kickTime
                , 'displayName', displayName, 'phone', phone, 'created', t.dCreated)
            )
            from 
            (select t1.*, t2.*, t1.created as dCreated from room_participants t1 
                left join users t2 on t1.userId = t2.id 
                where t1.roomId = ROOMID_ limit 5 offset countt) t);

answered Nov 13, 2019 at 2:12

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

1 Comment

Sliq Over a year ago

did this work? I see a LIMIT inside a subquery, which doesn't work in MySQL according to the official docs

Vikas Godara · Accepted Answer · 2022-06-01 13:05:48Z

I had the same problem here is my take on it

with cte as (select JSON_OBJECT('id', room_participants.id, 'isAdmin', room_participants.isAdmin, 'userId',
                            room_participants.userId, 'joinTime', room_participants.joinTime, 'leftTime',
                            room_participants.leftTime, 'status', room_participants.status, 'kickTime',
                            room_participants.kickTime, 'displayName', users.displayName, 'phone', users.phone,
                            'created', created) single_row
         from room_participants
                  LEFT JOIN users
                            ON room_participants.userId = users.id
         WHERE room_participants.roomId = ROOMID_
         LIMIT 5 OFFSET countt) select json_arrayagg(single_row) from cte

Collectives™ on Stack Overflow

Why the LIMIT are ignored when SELECT using JSON_ARRAYAGG?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related