MYSQL:: Stored Procedure where condition is not working

Question

i am trying to build json object from MYSQL select and pass it to out param. everything is working fine, but my where condition is not applying when i am using json_object, json_arrayagg, json_objectagg.

my select query is,

SELECT   json_object('arraylist',json_arrayagg(json_object('id',id,'code', code, ..., ..., ...))) AS jsonobj
FROM     normal_table a 
WHERE    a.code='826' 
AND      a.status='true' 
AND      a.vb_code ='124' 
ORDER BY a.date_ts DESC limit 3;

for this query all the records from table is getting populated.

if i remove like below

SELECT   json_object('id',id,'code', code, ..., ..., ...) AS jsonobj 
FROM     normal_table a 
WHERE    a.code='826' 
AND      a.status='true' 
AND      a.vb_code ='124' 
ORDER BY a.date_ts DESC limit 3;

it returns only 3 rows as expected.

what am i doing wrong here....

Answer 1

If you want to limit the number of rows that are aggregated, you need to use a subquery to limit the rows that are selected, then aggregate in the main query.

SELECT json_object('arraylist',json_arrayagg(json_object('id',id,'code', code, ..., ..., ...))) AS jsonobj
FROM (
    SELECT *
    FROM     normal_table a 
    WHERE    a.code='826' 
    AND      a.status='true' 
    AND      a.vb_code ='124' 
    ORDER BY a.date_ts
    DESC limit 3
) AS x;