-2

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

enter image description here

When I run below query,

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

I get following result

enter image description here

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.

Akina
  • 39,301
  • 5
  • 14
  • 25
micronyks
  • 54,797
  • 15
  • 112
  • 146
  • I don't think, that there is a way to get rows as arrays. Depending on how you want to use the data, there might be a way to use it in its original form. – Geshode Sep 27 '22 at 05:37
  • [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), espesially "5. Some Additional Suggestions". – Akina Sep 27 '22 at 05:39

1 Answers1

1

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.

Mihe
  • 2,270
  • 2
  • 4
  • 14
  • This is giving weird result set – micronyks Sep 27 '22 at 06:23
  • @micronyks, I've added a fiddle. Why do you think, it's weird? – Mihe Sep 27 '22 at 06:27
  • Thanks for the fiddle. it worked now. I really appreciate your answer and efforts but here it looks you are making an individual queries for each column eg. Rupee, Yen. As I already mentioned in my actual DB, I have at least 100 currencies (eg. Ruppe, Yen, Pound etc) and for each currency I have million of records. Is there any way to make it dynamic ? Also the problem would be, for me, I have separate DBs for each customer. `Your answer is really great by the way`. – micronyks Sep 27 '22 at 06:34
  • Probably this would help you understand (up to some extend) what I mean : https://stackoverflow.com/questions/73852739/how-to-execute-same-queries-with-different-column-names-against-different-databa – micronyks Sep 27 '22 at 06:38
  • Thanks for your kind feedback. The problem is, that your currencies are stored in different columns instead of different rows. Usually it requires you to name each column (btw: the accepted answer in the linked question does this, too) explicitly. You could also use dynamic SQL (see [here](https://stackoverflow.com/questions/13944417/mysql-convert-column-to-row-pivot-table) for an example). I'll try something different, but this will take a few hours as I'm busy. – Mihe Sep 27 '22 at 06:54
  • Thanks for pointing it out. Yeah probably that is what I'm looking for (but not sure). Yeah pls take your time but if you could help me. – micronyks Sep 27 '22 at 07:03
  • One more thing: do you use MySQL 8? – Mihe Sep 27 '22 at 07:11
  • How can I check it? I'm using mysql db from AWS RDS service. SO my mysql is on AWS. – micronyks Sep 27 '22 at 07:14
  • Just do a `SELECT version();` – Mihe Sep 27 '22 at 07:29
  • It is showing `5.7.38-log` – micronyks Sep 27 '22 at 07:32
  • Ok, didn't work. You'll have to go the dynamic SQL path :-( – Mihe Sep 27 '22 at 16:16
  • Can you pls help me? In real implementation it is giving me below result : { `"json_arrayagg(obj)": "[\"{\\\"datapoints\\\": [[\\\"2018-04-16 16:01:00.000000\\\", 19.510161666667]], \\\"currency\\\": \\\"Rupee\\\"}\", \"{\\\"datapoints\\\": [[\\\"2018-04-16 16:01:00.000000\\\", -166.767015], [\\\"2018-04-16 16:04:00.000000\\\", -166.782305]], \\\"currency\\\": \\\"pound\\\"}\"]" }` why is it `givng json.arrayagg(obj)` as key ? and also as you can see it is giving `\\\` back slash everywhere. any idea ? – micronyks Sep 28 '22 at 07:10
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/248401/discussion-between-mihe-and-micronyks). – Mihe Sep 28 '22 at 07:15
  • Can you pls pls pls help me on https://stackoverflow.com/questions/73944429/stored-procedure-to-get-a-desire-result-set-to-improve-performance-mysql ? – micronyks Oct 04 '22 at 07:34