JSON_ARRAYAGG(json_object - Performance Issue

[Binoy P]

Hi,

Please guide us to resolve the performance issue w.r.t usage of JSON_ARRAYAGG(json_object.

Background : A query with JSON_ARRAYAGG takes more than expected time, hence leading to application performance issue.
We checked the following in executing the query directly in DB.

1. Query using JSON_ARRAYAGG, takes lot of time to fetch the results

2. Same Query with out JSON_ARRAYAGG, results are getting fetched much faster.

Request your guidance, we are using limit as well to restrict the fetch to a limited rows.

the following is the actual query -

SELECT JSON_ARRAYAGG(json_object(
'ID', ID,
'username', username,
'first_name', first_name,
'last_name', COALESCE(last_name, ''),
'email', email,
#'password', password,
'country_code', country_code,
'mobile', mobile,
'circle', circle,
'sol_id', sol_id,
'sol_name', sol_name,
'facility_id', facility_id,
'access_point_name', access_point_name,
'sub_division_id', sub_division_id,
'department', department,
'department_desc', ippb.GetLovDec('DEPT', department),
'designation', designation,
'is_active', is_active,
'status', status,
'created_at', created_at,
'updated_at', updated_at
))
Into userInfoMsg
FROM db.users
Where ID = Case When tUserID = '{ALL}' Then ID Else tUserID End
And username Like Case When tUserName Is Null Then username Else Concat(Concat('%', tUserName), '%') End
#And Concat(First_name, Last_Name) Like Case When tName Is Null Then Concat(First_name, Last_Name) Else Concat(Concat('%', tName), '%') End
And First_name Like Case When tName Is Null Then First_name Else Concat(Concat('%', tName), '%') End
And mobile Like Case When tMobileNo Is Null Then mobile Else Concat(Concat('%', tMobileNo), '%') End
#And department Like Case When tDepNo Is Null Then department Else Concat(Concat('%', tDepNo), '%') End
And COALESCE(department, '~') Like Case When Null Is Null Then COALESCE(department, '~') Else Concat(Concat('%', Null), '%') End
And circle Like Case When tCircle Is Null Then circle Else Concat(Concat('%', tCircle), '%') End
Limit 1,1