73

How create json format with group-concat mysql?

(I use MySQL)

Example1:

table1:

email            |    name  |   phone
-------------------------------------
[email protected]    | Ben      | 6555333
[email protected]    | Tom      | 2322452
[email protected]    | Dan      | 8768768
[email protected]    | Joi      | 3434356

like syntax code that not give me the format:

select email, group-concat(name,phone) as list from table1 
group by email

output that I need:

email         |    list
------------------------------------------------
[email protected] |  {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"}
[email protected] |  {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"}
Improve this question
2
  • 1
    If your database is going to grow, this is a bad idea. Better do it using code in your application. Commented Sep 20, 2012 at 11:48
  • db static for readonly parpose Commented Sep 20, 2012 at 11:49

8 Answers 8

Reset to default
118

With the newer versions of MySQL, you can use JSON_OBJECT function to achieve the desired result, like so:

GROUP_CONCAT(
  JSON_OBJECT(
    'name', name,
    'phone', phone
  )
) AS list

To get the SQL response ready to be parsed as an array:

CONCAT(
  '[',
  GROUP_CONCAT(
    JSON_OBJECT(
      'name', name,
      'phone', phone
    )
  ),
  ']'
) AS list

This will give you a string like: [{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}] which can be JSON parsed.

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

4 Comments

Is there a shorthand like -> or ->> but for the CONCAT('[', GROUP_CONCAT(JSON_OBJECT(...) SEPARATOR ','), ']') ?
Any suggestions on how to use IF to omit the record if its blank? How its written returns {"name": null, "phone": null} if its an empty record set.
for me, the result isn't quite right.. the result is [{\"name\": \"ABC\", \"phone\": \"111\"}, {\"name\": \"DEF\", \"phone\": \"222\"}] .. its escaping all the double quotes around the field key and values.
found similar: stackoverflow.com/questions/59736028/…
85

Try this query -

SELECT
  email,
  GROUP_CONCAT(CONCAT('{name:"', name, '", phone:"',phone,'"}')) list
FROM
  table1
GROUP BY
  email;

JSON format result -

+---------------+-------------------------------------------------------------+
| email         | list                                                        |
+---------------+-------------------------------------------------------------+
| [email protected] | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |
| [email protected] | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |
+---------------+-------------------------------------------------------------+
Improve this answer

7 Comments

what would happen if name contains a double quote?
It depends on ANSI_QUOTES SQL mode, if it is activated - then you should double " in names, othervise - it will work.
sorry to say this is not a valid JSON... a JSON array is enclosed between [ and ]
As a sidenote to my edit, you could use the new JSON functions in MySQL 5.7 if that is an option for you. See : dev.mysql.com/doc/refman/5.7/en/…
@AntonioOrtells, to make it valid you would, and I did, just adjust the part with CONCAT('[', GROUP_CONCAT(…), ']') list, this wraps it in array brackets and the comma from GROUP_CONCAT does the rest.
|
42

For Mysql 5.7.22+

    SELECT
        email,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', name,
                'phone', phone
            )
        ) AS list
    FROM table1
    GROUP BY email;

Result:

+---------------+-------------------------------------------------------------------+
| email         | list                                                              |
+---------------+-------------------------------------------------------------------+
| [email protected] | [{"name":"Ben", "phone":6555333},{"name":"Joi", "phone":3434356}] |
| [email protected] | [{"name":"Tom", "phone":2322452},{"name":"Dan", "phone":8768768}] |
+---------------+-------------------------------------------------------------------+

The only difference is that column list is now Json-valid, so you can parse directly as Json

Improve this answer

2 Comments

The concat() solution trimmed the result, this one not!!
just a note that json_arrayagg does not define the order it will use. So at any given time/release, the order you get back may change (eg Ben's object may appear 2nd and Joi first, in the first array returned). A work around to this is to use group concat with an order by.. until mysql gets the 'order by' working with json_arrayagg
30

I hope this finds the right eyes.

You can use:

For arrays (documentation): 

JSON_ARRAYAGG(col_or_expr) as ...

For objects (documentation):

JSON_OBJECTAGG(key, value) as ...
Improve this answer

2 Comments

Important to note that those are available in 5.7.22+ as per this
Neither they available in Amazon aurora 5.7 at the time of writing this comment
19

Devart's answer above is great, but K2xL's question is valid. The answer I found was to hexadecimal-encode the name column using HEX(), which ensures that it will create valid JSON. Then in the application, convert the hexadecimal back into the string.

(Sorry for the self-promotion, but) I wrote a little blog post about this with a little more detail: http://www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in-mysql-using-group_concat/

[Edit for Oriol] Here's an example:

SELECT email,
    CONCAT(
        '[',
        COALESCE(
            GROUP_CONCAT(
                CONCAT(
                    '{',
                    '\"name\": \"', HEX(name), '\", ',
                    '\"phone\": \"', HEX(phone), '\"',
                    '}')
                ORDER BY name ASC
                SEPARATOR ','),
            ''),
        ']') AS bData
FROM table
GROUP BY email

Also note I've added a COALESCE in case there are no items for that email.

Improve this answer

5 Comments

Add the complex concat example in your answer. I think it's very helpful ;-)
This solved my issue, other solutions failed when decoding the resulting json with invalid chars like tabs, slashes, ...
the name and phone keys have to be in ""(double quotes) to represent a valid json
kekko12: Fixed. Thanks!
TO_BASE64 might lower the overcost that HEX introduce (ie: strings are shorter). But use stackoverflow.com/a/40235188/2342518 for nowadays MySQLs
6

Similar to Madacol's answer above, but slightly different. Instead of JSONARRAYAGG, you could also CAST AS JSON:

SELECT
        email,
       CAST( CONCAT(
        '[', 
           GROUP_CONCAT(
           JSON_OBJECT(
              'name', name,
              'phone', phone
            )
        ),']') AS JSON )
    FROM table1
    GROUP BY email;

Result:

+---------------+-------------------------------------------------------------------+
| email         | list                                                              |
+---------------+-------------------------------------------------------------------+
| [email protected] | [{"name":"Ben", "phone":6555333},{"name":"Joi", "phone":3434356}] |
| [email protected] | [{"name":"Tom", "phone":2322452},{"name":"Dan", "phone":8768768}] |
+---------------+-------------------------------------------------------------------+
Improve this answer

3 Comments

what is your DBMS you can use json path in sql server
sorry i missed that..This is for Mysql 5.7.26
the only thing to keep in mind like when you use GROUP_CONCAT you can have only 1024 characters only. If your JSON has more length then I would suggest the below answer to follow. stackoverflow.com/a/58509829/8197832
4

Going off of @Devart's answer... if the field contains linebreaks or double quotation marks, the result will not be valid JSON.

So, if we know the "phone" field occasionally contains double-quotes and linebreaks, our SQL would look like: 

SELECT
  email,
  CONCAT(
    '[',
    GROUP_CONCAT(CONCAT(
        '{name:"', 
        name, 
        '", phone:"', 
        REPLACE(REPLACE(phone, '"', '\\\\"'),'\n','\\\\n'), 
        '"}'
      )),
    ']'
  ) AS list
FROM table1 GROUP BY email;

If Ben phone has a quote in the middle of it, and Joi's has a newline, the SQL would give (valid JSON) results like:

[{name:"Ben", phone:"655\"5333"},{name:"Joi", phone:"343\n4356"}]
Improve this answer

1 Comment

Hello, Jonathan could you take a look on my question? stackoverflow.com/questions/75136136/…
0

Use like this 

SELECT email,concat('{name:"',ur_name_column,'",phone:"',ur_phone_column,'"}') as list FROM table1 GROUP BY email;

Cheers

Improve this answer

1 Comment

Will throw a warning (and inconsistant results) since you're retrieving a non-grouped non-unique column with a GROUP BY clause (and will be prone to JSOn-injection if ur_name_column contains double quotes or blackslash)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.