How create json format with group-concat mysql?

Question

(I use MySQL)

Example1:

table1:

email            |    name  |   phone
-------------------------------------
my1@gmail.com    | Ben      | 6555333
my2@gmail.com    | Tom      | 2322452
my2@gmail.com    | Dan      | 8768768
my1@gmail.com    | 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
------------------------------------------------
my1@gmail.com |  {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"}
my2@gmail.com |  {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"}

Thanks

If your database is going to grow, this is a bad idea. Better do it using code in your application. — oxygen, Sep 20 '12 at 11:48

score 110 · Answer 1 · edited Dec 24 '22 at 02:52

110

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.

edited Dec 24 '22 at 02:52

starball

20,030
7
43
238

answered Oct 25 '16 at 08:36

myusuf

11,810
11
35
50

Is there a shorthand like `->` or `->>` but for the `CONCAT('[', GROUP_CONCAT(JSON_OBJECT(...) SEPARATOR ','), ']')` ? – Xenos May 17 '18 at 10:05
great, save my time. – Wooyoung Tyler Kim Jun 27 '18 at 07:05
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. – hamncheez Jul 31 '19 at 20:53

Devart · Accepted Answer · 2012-09-20T12:25:29.300

84

Try this query -

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

JSON format result -

+---------------+-------------------------------------------------------------+
| email         | list                                                        |
+---------------+-------------------------------------------------------------+
| my1@gmail.com | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |
| my2@gmail.com | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |
+---------------+-------------------------------------------------------------+

edited Sep 20 '12 at 12:25

answered Sep 20 '12 at 12:06

Devart

119,203
23
166
186

9

what would happen if name contains a double quote? – K2xL Dec 20 '13 at 20:17
It depends on ANSI_QUOTES SQL mode, if it is activated - then you should double " in names, othervise - it will work. – Devart Dec 23 '13 at 07:26
hmm this seems to surround the entire "list" json value with double quotes - meaning if you json_encode the entire result the field list will not be encoded and remains a string... – K2xL Jan 08 '14 at 21:19
5

sorry to say this is not a valid JSON... a JSON array is enclosed between [ and ] – Antonio Ortells Aug 17 '16 at 17:17
1

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 : https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-object – Mathieu de Lorimier Oct 21 '16 at 13:59
4

@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. – Adrian Föder May 18 '18 at 06:55
Just concatenating variable string content into a JSON object is likely to result in errors and open to injection attacks which could have unexpected security implications. I woulc not recommend this solution without properly escaping the variables that go in the JSON string. – Ivo Smits Dec 20 '21 at 14:17

Madacol · Answer 3 · 2020-02-13T15:40:08.747

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                                                              |
+---------------+-------------------------------------------------------------------+
| my1@gmail.com | [{"name":"Ben", "phone":6555333},{"name":"Joi", "phone":3434356}] |
| my2@gmail.com | [{"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

The concat() solution trimmed the result, this one not!! – Matías Cánepa Feb 26 '22 at 00:27 — Matías Cánepa, Feb 26 '22 at 00:27

score 30 · Answer 4 · edited Apr 22 '19 at 23:28

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

edited Apr 22 '19 at 23:28

aero

1,654
1
21
31

answered Jan 18 '19 at 19:48

Maciek Semik

1,872
23
43

7

Important to note that those are available in 5.7.22+ as per [this](https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html) – Mathieu de Lorimier Mar 07 '19 at 20:27
2

Neither they available in Amazon aurora 5.7 at the time of writing this comment – baldrs May 29 '19 at 11:18

alexkorn · Answer 5 · 2018-04-05T18:16:20.833

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.

edited Apr 05 '18 at 18:16

answered May 12 '15 at 18:58

alexkorn

315
2
6

Add the complex concat example in your answer. I think it's very helpful ;-) – Oriol Feb 09 '17 at 23:18
This solved my issue, other solutions failed when decoding the resulting json with invalid chars like tabs, slashes, ... – Mirko Oct 04 '17 at 09:53
1

the `name` and `phone` keys have to be in `""`(double quotes) to represent a valid json – kekko12 Apr 04 '18 at 17:00
kekko12: Fixed. Thanks! – alexkorn Apr 05 '18 at 18:16
`TO_BASE64` might lower the overcost that `HEX` introduce (ie: strings are shorter). But use https://stackoverflow.com/a/40235188/2342518 for nowadays MySQLs – Xenos May 17 '18 at 10:05

score 5 · Answer 6 · answered Jun 10 '20 at 19:29

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                                                              |
+---------------+-------------------------------------------------------------------+
| my1@gmail.com | [{"name":"Ben", "phone":6555333},{"name":"Joi", "phone":3434356}] |
| my2@gmail.com | [{"name":"Tom", "phone":2322452},{"name":"Dan", "phone":8768768}] |
+---------------+-------------------------------------------------------------------+

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. https://stackoverflow.com/a/58509829/8197832 — Subham, Oct 21 '20 at 07:30

Jonathan Harford · Answer 7 · 2017-02-24T21:41:31.543

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"}]

Hello, Jonathan could you take a look on my question? https://stackoverflow.com/questions/75136136/replace-with-array-from-a-select-group-concat — Sophie, Jan 17 '23 at 07:32

score 0 · Answer 8 · answered Sep 20 '12 at 12:06

0

Use like this

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

Cheers

answered Sep 20 '12 at 12:06

Sundar G

1,069
1
11
29

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) – Xenos May 17 '18 at 10:07

How create json format with group-concat mysql?

8 Answers8

For Mysql 5.7.22+

Result:

Linked

Related