How to convert result table to JSON array in MySQL

Question

I'd like to convert result table to JSON array in MySQL using preferably only plain MySQL commands. For example with query

SELECT name, phone FROM person;

| name | phone |
| Jack | 12345 |
| John | 23455 |

the expected JSON output would be

[
  {
    "name": "Jack",
    "phone": 12345
  },
  {
    "name": "John",
    "phone": 23455
  }
]

Is there way to do that in plain MySQL?

EDIT:

There are some answers how to do this with e.g. MySQL and PHP, but I couldn't find pure MySQL solution.

At the moment I'm running this from mysql console and bash scripts to export some specific data. — ronkot, Jan 20 '17 at 09:03
I could think many useful cases for this. For example, querying some JSON data from database and piping it to POST request. — ronkot, Jan 20 '17 at 09:06
though a bit late, I think the answers should have mentioned this, `json_object` works only for `MySQL 5.7` and higher — Anusha, Nov 07 '17 at 00:04
@Strawberry - one use case is to easily generate test data to use as input to a module that requires json as input. Or any other "one-off" task where the *requirement* is to produce json. In my case, I've already got the mysql database sitting there, and various sql query snippets. Just remote in via phpmyadmin or mysql workbench, and tweak the query to produce json. Don't need to connect php to the db... — ToolmakerSteve, Mar 06 '19 at 23:50
@toolmakersteve Cheers; I've been waiting 2 years for that little nugget — Strawberry, Mar 07 '19 at 00:16
@Strawberry The use case I'm currently investigating is to update a JSON column in table A using the result of a query against table B. Formatting data as JSON allows you to do this in a single query. — Jordan Dodson, Aug 01 '19 at 21:51

ronkot · Accepted Answer · 2019-04-29T06:17:19.687

196

New solution:

Built using Your great comments, thanks!

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;

Old solution:

With help from @Schwern I managed to put up this query, which seems to work!

SELECT CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)),
    ']'
) 
FROM person;

edited Apr 29 '19 at 06:17

answered Jan 20 '17 at 09:28

ronkot

5,915
4
27
41

6

If you're using the mysql client, [you can use `--json` instead](https://dev.mysql.com/doc/refman/5.7/en/mysql-shell-json-output.html) then it will work on any query. – Schwern Feb 18 '18 at 05:41
4

Ouput may be truncated due to group_concat_max_len https://stackoverflow.com/questions/26553823/what-is-the-maximum-allowance-for-group-concat-max-len-in-mysql – Laurent W. Mar 23 '18 at 13:26
3

Why not use, `JSON_ARRAY(JSON_OBJECT('name', name, 'phone', phone))` instead? – DarckBlezzer May 12 '18 at 20:02
6

@DarckBlezzer the output from your proposition seems to be a collection of lists: `[{"name": "Jack", "phone": "12345"}] [{"name": "John", "phone": "23455"}]` (see [this example](https://paiza.io/projects/nDa_N94y3O2buGkUVvk55g?language=mysql)) – ronkot May 14 '18 at 06:30
2

@DarckBlezzer Instead of `JSON_ARRAY` rather `JSON_ARRAYAGG` – Csaba Toth Mar 14 '19 at 21:47
@CsabaToth Thanks for your input, I added new solution which is really neat! – ronkot Apr 03 '19 at 05:41
@Schwern Link is dead. Moreover trying to use `--json` results in unknown option for me on version 5.7.18. – Giacomo Alzetta Oct 17 '19 at 11:03
2

@GiacomoAlzetta It's for the MySQL shell, mysqlsh, distinct from the MySQL client. https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html#option_mysqlsh_json – Schwern Oct 18 '19 at 00:41
1

Wow this is the most amazing stuff I've seen for quite a while! Thank you so much! – Chen Ni Jun 22 '21 at 07:23
what if there are lot of columns in table and mentioning each column name is rigorous task, how to handle this?? – Muhammad Awais Apr 12 '22 at 19:50
`JSON_ARRAYGG` won't work older than v5.7.22 as [docs](https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html) says. – Mejan Jul 29 '22 at 21:53

score 57 · Answer 2 · answered Jan 20 '17 at 08:29

57

You can use json_object to get rows as JSON objects.

SELECT json_object('name', name, 'phone', phone)
FROM person;

This won't put them in an array, or put commas between them. You'll have to do that in the code which is fetching them.

answered Jan 20 '17 at 08:29

Schwern

153,029
25
195
336

1

Thanks, this is already very near! I'm still experimenting on how to construct an array from these object. – ronkot Jan 20 '17 at 09:04
2

I got this `ERROR 1305 (42000): FUNCTION mydb.JSON_OBJECT does not exist` error. How can I ensure this function exists? – Anthony Kong Dec 27 '17 at 05:40
3

@AnthonyKong what version are you using? `json_object` can only be use in `5.7` and higher.. – Shadow Fiend Dec 27 '17 at 07:44
2

I prefer this over the selected "right" answer as it returns each entry as it's own row. With this you can stream results back. – Sean256 Feb 18 '18 at 04:17

score 20 · Answer 3 · answered Jun 30 '20 at 13:25

20

If you're stuck on MySQL 5.6 like me, try this:

SELECT
    CONCAT(
       '[',
       GROUP_CONCAT(
           CONCAT(
               '{"name":"', name, '"',
               ',"phone":"', phone, '"}'
           )
       ),
       ']'
    ) as json
FROM person

answered Jun 30 '20 at 13:25

Brendan

809
1
8
13

Thanks for the solution for older versions of mysql – dmSherazi Jul 07 '21 at 12:39
should probably escape any quotes in the strings themselves – Garr Godfrey Dec 09 '21 at 21:18
This worked for me, but indeed like @GarrGodfrey is saying you'll need to manually escape backslashes (do that one first before adding more backslashes), quotes, new-line/carriage returns, etc. – asontu Nov 17 '22 at 13:37
As well, `group_concat()` returns `null` when even just one of the values is `null`. So in the above example, if `phone` is `null` but `name` is not, the entire query will return `null`. So be sure to place `',"phone":"', ifnull(phone, ''), '"}'` if you want empty strings or `',"phone":', ifnull(concat('"', phone, '"'), 'null'), '}'` if you want `null` in the JSON. Finally. `group_concat()` by default has a max length of 1024 chars. You can set it higher with `set @@group_concat_max_len = 100000;` – asontu Nov 18 '22 at 10:27

score 16 · Answer 4 · edited Jun 30 '20 at 13:33

16

There are two "group by" functions for JSON called json_arrayagg, json_objectagg.

This problem can be solved with:

SELECT json_arrayagg(
    json_merge(
          json_object('name', name), 
          json_object('phone', phone)
    )
) FROM person;

This requires MySQL 5.7+.

edited Jun 30 '20 at 13:33

TylerH

20,799
66
75
101

answered Feb 06 '19 at 20:36

mikeryder

161
1
4

score 7 · Answer 5 · edited Aug 28 '22 at 00:16

If you need a nested JSON Array Object, you can join JSON_OBJECT with json_arrayagg as below:

{
    "nome": "Moon",
    "resumo": "This is a resume.",
    "dt_inicial": "2018-09-01",
    "v.dt_final": null,
    "data": [
        {
            "unidade": "unit_1",
            "id_unidade": 9310
        },
        {
            "unidade": "unit_2",
            "id_unidade": 11290
        },
        {
            "unidade": "unit_3",
            "id_unidade": 13544
        },
        {
            "unidade": "unit_4",
            "id_unidade": 13608
        }
    ]
}

You can also do it like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_lst_caso`(
IN `codigo` int,
IN `cod_base` int)
BEGIN

    DECLARE json TEXT DEFAULT '';

    SELECT JSON_OBJECT(
        'nome', v.nome, 
        'dt_inicial', v.dt_inicial, 
        'v.dt_final', v.dt_final, 
        'resumo', v.resumo,
        'data', ( select json_arrayagg(json_object(
                                'id_unidade',`tb_unidades`.`id_unidade`,
                                'unidade',`tb_unidades`.`unidade`))
                            from tb_caso_unidade
                                INNER JOIN tb_unidades ON tb_caso_unidade.cod_unidade = tb_unidades.id_unidade
                            WHERE tb_caso_unidade.cod_caso = codigo)
    ) INTO json
    FROM v_caso AS v
    WHERE v.codigo = codigo and v.cod_base = cod_base;
    
    SELECT json;
    
END

I have error Error Code: 1582. Incorrect parameter count in the call to native function 'json_object' query: SELECT v.*, p.*, (select json_arrayagg(json_object(size, sku, quantity)) from variant where variation_id = v.id and product_id = p.id) as variant FROM variation v join product p on v.product_id = p.id; — rajkanani, Apr 04 '22 at 06:18

score 0 · Answer 6 · answered Jan 31 '23 at 15:56

0

For most situations, I use DataGreap, but for big tables, it is not work.

My GIST shell script

answered Jan 31 '23 at 15:56

Igor Stetsyuk

1
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 02 '23 at 11:23

How to convert result table to JSON array in MySQL

6 Answers6

Linked