0

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:

enter image description here

enter image description here

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

Ramaraju.d
  • 1,301
  • 6
  • 26
  • 46
  • Provide complete sample (CREATE TABLE + INSERT INTO + query + desired output). – Akina Jul 11 '22 at 18:43
  • @Akina the data is taken from 3-4 tables. They are in 3NF. So I am procedure I have written a query to form a JSON. Unfortunately the sort isn't working as expected. I was expecting last update row to appear at the top of the list. Added the query and expected o/p for your reference. – Ramaraju.d Jul 11 '22 at 19:51
  • Make a model. Build CEATE TABLE which matches your query output (skip unrelated columns), add 3-5 rows of your output as INSERT INTO and provide desired final rows ordering. Now it is even impossible to distinguish does the values shown are the values returned in one row or in a lot of rows... – Akina Jul 11 '22 at 19:54
  • @Akina The value returned from the SQL will always be 1 row containing the JSON array output. – Ramaraju.d Jul 11 '22 at 20:15
  • 1
    So what do you want to sort - the array elements in this array? If so then you'd parse the array to separate elements then reconstruct the array in needed order. [DEMO](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f6896bf19063c51bd4af74368e651aa7) (see CTE only, outer query simply formats the value to pretty readable text). – Akina Jul 12 '22 at 04:15
  • Wow, I just learnt a new concept. I got the desired results with little tweaks. Thanks for the solution. Requesting you to please add this as an Answer. – Ramaraju.d Jul 12 '22 at 17:14
  • @Akina Can you please help me how can I sort based on the inner key. Like Quantity in my case, I have added the snippet in the post. – Ramaraju.d Sep 19 '22 at 10:25

0 Answers0