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.

Sweven

A combination of GROUP_CONCAT and CONCAT – Verret

How are you running this query? – Paragraph

At the moment I'm running this from mysql console and bash scripts to export some specific data. – Sweven

I could think many useful cases for this. For example, querying some JSON data from database and piping it to POST request. – Sweven

though a bit late, I think the answers should have mentioned this, json_object works only for MySQL 5.7 and higher – Certes

@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... – Duro

@toolmakersteve Cheers; I've been waiting 2 years for that little nugget – Posner

@Posner 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. – Amazonite

Answer 1

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;

Sweven

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

Ouput may be truncated due to group_concat_max_len #26554323 – Southbound

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

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

@Dionysus Instead of JSON_ARRAY rather JSON_ARRAYAGG – Africander

@CsabaToth Thanks for your input, I added new solution which is really neat! 💪 – Sweven

@Paragraph Link is dead. Moreover trying to use --json results in unknown option for me on version 5.7.18. – Rhizopod

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

Wow this is the most amazing stuff I've seen for quite a while! Thank you so much! – Aether

what if there are lot of columns in table and mentioning each column name is rigorous task, how to handle this?? – Unfair

JSON_ARRAYGG won't work older than v5.7.22 as docs says. – Aperture

Answer 2

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.

Paragraph

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

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

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

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. – Avery

Answer 3

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

Trochaic

Thanks for the solution for older versions of mysql – Lowboy

should probably escape any quotes in the strings themselves – Heliotaxis

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. – Alphitomancy

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; – Alphitomancy

Answer 4

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+.

Jealous

Answer 5

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

Rehabilitation

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; – Pain

Answer 6

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

My GIST shell script

Unknot

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. – Welbie