I have a JSON returned from a SQL which is created using json_arrayagg and JSON_OBJECT. Have applied sort order based on timestamp of column t_last_updated_at. The output of sql is stored in json_output variable in mysql procedure.
SQL:
select json_arrayagg(JSON_OBJECT('id',id,'p_id',p_id,'e_number',e_number,
't_last_updated_at',t_last_updated_at,
'List',
(select json_arrayagg(JSON_OBJECT('main_id',main_id,'quantity',quantity,'unitprice',unitprice,'created_at',created_at))
from t_sampling s where s.id=o.id)
)
) into json_output
from t_orm o where o.id=_id
order by o.t_last_updated_at desc;
Post Query execution:
json_output= [
{
"id": 13,
"p_id":19,
"List": [
{
"main_id": 99,
"quantity": 1,
"unitprice": 31,
"created_at": "2022-06-26 08:25:33.000000"
}
],
"e_number": 10,
"t_last_updated_at": "2022-06-26 08:25:33.000000"
},
{
"id": 13,
"p_id":100,
"List": [
{
"main_id": 919,
"quantity": 11,
"unitprice": 23.5,
"created_at": "2022-05-26 08:25:33.000000"
}
],
"e_number": 10,
"t_last_updated_at": "2022-07-11 08:30:33.000000"
}]
Realizing that the sort order isn't in my control, I tried to use
order by cast('$.t_last_updated_at' as DATETIME) DESC
Still I don't get desired result where "p_id":100, should be at top of the list.
Expected o/p:
[
{
"id": 13,
"p_id":100,
"List": [
{
"main_id": 919,
"quantity": 11,
"unitprice": 23.5,
"created_at": "2022-05-26 08:25:33.000000"
}
],
"e_number": 10,
"t_last_updated_at": "2022-07-11 08:30:33.000000"
},
{
"id": 13,
"p_id":19,
"List": [
{
"main_id": 99,
"quantity": 1,
"unitprice": 31,
"created_at": "2022-06-26 08:25:33.000000"
}
],
"e_number": 10,
"t_last_updated_at": "2022-06-26 08:25:33.000000"
}
]
Tables and Info:
Can someone correct me what is the mistake I am doing? or any better solution to achieve a sorted JSON ?
Update: I am now trying to sort based on the Quantity(which is inside the List array) Below is my change but unable to figure out how can I change my query to make the sort work
WITH cte AS (
SELECT DISTINCT
JSON_ARRAYAGG(jsontable.one_object) reordered_json
FROM test
CROSS JOIN JSON_TABLE(test.json_array,
'$[*]' COLUMNS (one_object JSON PATH '$' ,
NESTED PATH '$.List[*]' COLUMNS (q for ORDINALITY,
quantity INT path '$."quantity"'
))) as jsontable
order by q desc
)
SELECT JSON_PRETTY(cte.reordered_json) FROM cte
Any help please
