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.

though a bit late, I think the answers should have mentioned this, json_object works only for MySQL 5.7 and higher — Anusha
– Anusha, Commented Nov 7, 2017 at 0:04
@toolmakersteve Cheers; I've been waiting 2 years for that little nugget — Strawberry
– Strawberry, Commented Mar 7, 2019 at 0: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
– Jordan Dodson, Commented Aug 1, 2019 at 21:51

ronkot · Accepted Answer · 2019-04-29 06:17:19Z

218

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, 2019 at 6:17

answered Jan 20, 2017 at 9:28

ronkot

6,4674 gold badges31 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Schwern Over a year ago

If you're using the mysql client, you can use --json instead then it will work on any query.

Laurent W. Over a year ago

Ouput may be truncated due to group_concat_max_len stackoverflow.com/questions/26553823/…

DarckBlezzer Over a year ago

Why not use, JSON_ARRAY(JSON_OBJECT('name', name, 'phone', phone)) instead?

ronkot Over a year ago

@DarckBlezzer the output from your proposition seems to be a collection of lists: [{"name": "Jack", "phone": "12345"}] [{"name": "John", "phone": "23455"}] (see this example)

Schwern Over a year ago

@GiacomoAlzetta It's for the MySQL shell, mysqlsh, distinct from the MySQL client. dev.mysql.com/doc/mysql-shell/8.0/en/…

|

Schwern · Accepted Answer · 2017-01-20 08:29:19Z

63

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, 2017 at 8:29

Schwern

167k28 gold badges225 silver badges370 bronze badges

4 Comments

ronkot Over a year ago

Thanks, this is already very near! I'm still experimenting on how to construct an array from these object.

Anthony Kong Over a year ago

I got this ERROR 1305 (42000): FUNCTION mydb.JSON_OBJECT does not exist error. How can I ensure this function exists?

Shadow Fiend Over a year ago

@AnthonyKong what version are you using? json_object can only be use in 5.7 and higher..

Sean256 Over a year ago

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.

Brendan · Accepted Answer · 2020-06-30 13:25:43Z

21

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, 2020 at 13:25

Brendan

8291 gold badge8 silver badges13 bronze badges

4 Comments

dmSherazi Over a year ago

Thanks for the solution for older versions of mysql

Geoduck Over a year ago

should probably escape any quotes in the strings themselves

asontu Over a year ago

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 Over a year ago

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;

TylerH · Accepted Answer · 2020-06-30 13:33:18Z

18

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, 2020 at 13:33

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 6, 2019 at 20:36

mikeryder

1801 silver badge5 bronze badges

Comments

Stephen Ostermiller · Accepted Answer · 2022-08-28 00:16:39Z

8

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

edited Aug 28, 2022 at 0:16

Stephen Ostermiller♦

25.8k18 gold badges96 silver badges117 bronze badges

answered Jul 12, 2020 at 22:19

Juliano Costa

5337 silver badges10 bronze badges

1 Comment

rajkanani Over a year ago

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;

Igor Stetsyuk · Accepted Answer · 2023-01-31 15:56:42Z

1

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

My GIST shell script

answered Jan 31, 2023 at 15:56

Igor Stetsyuk

112 bronze badges

1 Comment

Community Over a year ago

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.

Collectives™ on Stack Overflow

How to convert result table to JSON array in MySQL

6 Answers 6

13 Comments

4 Comments

4 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

13 Comments

4 Comments

4 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related