How create json format with group-concat mysql?

How create json format with group-concat mysql?

(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

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. Hope this helps.

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

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

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

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.

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

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

Use like this

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

Cheers