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

You have "limit 3"...it will always return a max of 3 rows. What is your expectation? — cicit, Mar 02 '20 at 18:13
@Ci-CiMillsThomson, that is what i expecting, but it is returning all the rows from the table. — Jagath Kumar, Mar 02 '20 at 18:19
You have no `GROUP BY`, so this only returns 1 row that aggregates all the rows that match the `WHERE` clause. — Barmar, Mar 02 '20 at 18:31
`LIMIT` applies to the result after aggregating, not the number of rows that are aggregated. — Barmar, Mar 02 '20 at 18:32
Where is the stored procedure? This is just an ordinary query, it works the same in a SP. — Barmar, Mar 02 '20 at 18:32

score 0 · Accepted Answer · answered Mar 02 '20 at 18:34

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;

MYSQL:: Stored Procedure where condition is not working

1 Answers1