order by ASC|DESC in returning JSON object value - MySQL query

micronyks
November 27, 2023
181 views
0 votes
2 Answers

I have simple table as shown below,

datetime	Rupee	Pound
2019-02-16	80%	70%
2019-02-17	30%	60%
2019-02-18	67%	89%

And MySQL query looks like below,

SELECT json_arrayagg(obj)
FROM (
    SELECT json_object(
                      'currency', 'rupee',
                      'data', json_arrayagg(json_array(datetime, rupee))
                      ) AS obj
    FROM currency
    UNION ALL
    SELECT json_object(
                      'currency', 'pound',
                      'data', json_arrayagg(json_array(datetime, pound))
                      )
    FROM currency
   ) x;

Result

[{"currency": "rupee", "data": [["2019-02-16", "80%"], ["2019-02-17", "30%"], ["2019-02-18", "67%"]]},
 {"currency": "pound", "data": [["2019-02-16", "70%"], ["2019-02-17", "60%"], ["2019-02-18", "89%"]]}]

The result is good with no problem.

The thing I want is data to come in ASC|DESC order on Rupee or Pound column.

The expected result would look like below,

[{"currency": "Rupee", "data": [["2019-02-17", "30%"], ["2019-02-18", "67%"], ["2019-02-16", "80%"]]},
 {"currency": "pound", "data": [["2019-02-17", "60%"], ["2019-02-16", "70%"], ["2019-02-18", "89%"]]}]

I know order by but here where to apply it I don’t know. I don’t know even if it is possible in query itself.

Also, please note that Rupee & Pound columns are type of varchar(10)

DB Fiddle with sample data: https://dbfiddle.uk/_9g-CSjH

Answers

You can’t control the order of the elements in the array using json_arrayagg().

You can use group_concat() to manually generate the the ordered json arrays :

with cte as (
  select json_array(datetime, rupee) AS obj, rupee as value_currency, 'rupee' as currency
  from currency
  UNION ALL 
  select json_array(datetime, pound), pound, 'pound'
  from currency
),
cte2 as (
  select currency, 
        concat('[',
          group_concat(
            obj
            order by value_currency
          ),
          ']'
        ) obj
  from cte
  group by currency
)
select json_arrayagg(obj)
from (
  select json_object(
                  'currency', currency, 
                  'data', CAST( obj as JSON)
       ) AS obj
  from cte2
) x

Result :

[{"data": [["2019-02-17", "60%"], ["2019-02-16", "70%"], ["2019-02-18", "89%"]], "currency": "pound"}, {"data": [["2019-02-17", "30%"], ["2019-02-18", "67%"], ["2019-02-16", "80%"]], "currency": "rupee"}]

Demo here

The json_arrayagg cannot sort the data before aggregation. However, the group_concat function allows sorting the data inside each group before concatenation. You need to apply some manual JSON generation i.e. adding square brackets and commas:

with cte(currency, data) as (
    select
        'rupee',
        cast(concat(
            '[',
            group_concat(json_array(datetime, rupee) order by datetime separator ','), 
            ']'
        ) as json)
    from currency
    union all
    select
        'pound',
        cast(concat(
            '[', 
            group_concat(json_array(datetime, pound) order by datetime separator ','), 
            ']'
        ) as json)
    from currency
)
select
    json_pretty(cast(concat(
        '[', 
        group_concat(json_object('currency', currency, 'data', data) order by currency /* asc or desc*/ separator ','), 
        ']'
    ) as json)) as jsonnn
from cte

DB<>Fiddle

Please signup or login to give your own answer.

Click here to cancel reply.

order by ASC|DESC in returning JSON object value – MySQL query

Answers