CODEWITHSUNDEEP
mysqljson
PumpkinSeed
PumpkinSeed

Reputation: 3113

MySQL Select return as JSON

I read about the MySQL 5.7 features and I'm curious is it possible the return the given rows of the select in JSON format or return the whole result as JSON format. My data is looks like a normal MySQL table: 

| 13162 |         1 |  178803 | 093ea6a6-1077-4ea4-8a23-647e1d621b4a                                                   | 1485260588 | NULL  |
| 13163 |         1 |  178803 | 6f34579b-4745-4ec3-babd-ab12aab5b99c                                                   | 1485260718 | NULL  |
| 13165 |         1 |  178802 | 6f34579b-4745-4ec3-babd-ab12aab5b94c                                                   | 1485260705 | NULL  |
| 13166 |         1 |  178022 | 6f34579b-4745-4ec3-babd-ab12a4b5b94c                                                   | 1485260645 | NULL  |
| 13168 |         1 |  178022 | 6f34579b-4745-4ec3-babd-ab32a4b5b94c                                                   | 1485250645 | NULL  |
| 13169 |         1 |  178022 | 6f34579b-4745-4ec3-babd-ab32a4b5b945                                                   | 1475250645 | NULL  |
| 13170 |         1 |  176022 | 6f34579b-4745-4ec3-babd-ab32a4b5b9e5                                                   | 1465250645 | NULL  |
| 13171 |         1 |  170022 | 6f34579b-4745-4ec3-babd-ab32a4a5b9e5                                                   | 1425250645 | NULL  |
| 13172 |         1 |  170022 | 6f34579b-4745-4ec3-babd-ab32a4aeb9e5                                                   | 1426250645 | NULL  |
| 13173 |         1 |  178804 | ea7bcd3b-0f04-4ace-ba9b-a26d297029f7                                                   | 1486474710 | NULL  |
| 13174 |         1 |  178804 | 39855021-ceb8-4178-a3cc-0ba11cf4490a                                                   | 1486475039 | NULL  |
| 13175 |         1 |       2 | 6f34569b-4745-4ec3-b3bd-ab32a4aeb9e5                                                   |       NULL | NULL  |
| 13176 |         1 |       2 | 39855021-ceb8-4578-a3cc-0ba11cf4490a                                                   |       NULL | NULL  |
| 13177 |         1 |       2 | 39852021-ceb8-4578-a3cc-0ba11cf4490a                                                   |       NULL | NULL  |
| 13178 |         1 |       2 | 39855421-ceb8-4578-a3cc-0ba11cf4490a                                                   |       NULL | NULL  |

I'm using the SELECT * FROM AccessToken; query. How can I get back the result in JSON format? I wanted to do it on the database level, because when I have a big amount of data the programming level is too slow. I mean when I have 200k user to convert them to JSON using Go spent 8 seconds.

For example result: 

{
  [
    {
      "id": 1123,
      "token": "asdjkasldkjlasdjf"
    },
    {
      "id": 11323,
      "token": "asdjkasdasdasldkjlasdjf"
    }
  ]
}

Upvotes: 0

Views: 2006

Answers (1)

Stefano Zanini
Stefano Zanini

Reputation: 5916

The JSON in your example is not valid, but this should do what you desire

select  concat('[',
               group_concat(concat('{"id": "',
                                   col1,
                                   '", "token": "',
                                   col2,
                                   '"}'
                            ) separator ','),
               ']')
from    myTable

or, if you want to get an object instead of an array

select  concat('{ "array": [',
               group_concat(concat('{"id": "',
                                   col1,
                                   '", "token": "',
                                   col2,
                                   '"}'
                            ) separator ','),
               ']}')
from    myTable

The things you concatenate in the inner concat function will obviously depend on your table's column names.

Upvotes: 1

Related Questions