Transform returned result set to an appropriate JSON format-CodePudding

I have below table, This table has millions of records based on timestamp.

When I run below query,

Select datetime, Rupee, Yen 
from currency 
where datetime between value1 and value2

I get following result

Now it gets converted to JSON format, I get below result,

[
  {
    "datetime": "2019-02-16T10:40:00.000Z",
    "Rupee": 10,
    "Yen": 60
  },
  {
    "datetime": "2019-24-16T10:50:00.000Z",
    "Rupee": 30,
    "Yen": 70
  },
  {
    "datetime": "2019-02-16T10:55:00.000Z",
    "Rupee": 40,
    "Yen": 80
  },
  {
    "datetime": "2019-02-16T10:58:00.000Z",
    "Rupee": 50,
    "Yen": 90
  }
]

BUT I want to transform the result to below format,

[
  {
     "currency": "Rupee",
      "timeseriesdata": [
        ["2019-02-16T10:40:00.000Z",10],
        ["2019-24-16T10:50:00.000Z", 30],
        ["2019-02-16T10:55:00.000Z", 40],
        ["2019-02-16T10:58:00.000Z", 50]
      ]
  },
  {
    "currency": "Yen",
      "timeseriesdata": [
        ["2019-02-16T10:40:00.000Z",60],
        ["2019-24-16T10:50:00.000Z", 70],
        ["2019-02-16T10:55:00.000Z", 80],
        ["2019-02-16T10:58:00.000Z", 90]
      ]
  }
]

Is there any way to do it using MYSQL query/stored procedure/functions ? is it even possible ?

Pls note I can transform it in Node/Javascript but returned result set would contain million of records with hundred of currencies in real life scenario. So I don't think it makes sense to convert it in Node/Javascript. OR I don't know how should I deal with this scenario.

CodePudding user response：

You could use a combination of json_object, json_array and json_arrayagg:

SELECT json_arrayagg(obj) 
  FROM (SELECT json_object('currency', 'Rupee', 
                           'timeseriesdata', json_arrayagg(json_array(datetime, rupee))) AS obj 
          FROM currency
        UNION ALL 
        SELECT json_object('currency', 'Yen', 
                           'timeseriesdata', json_arrayagg(json_array(datetime, yen))) 
          FROM currency
       ) x;

Here's a fiddle.