group_concat - ¿Cómo crear formato json con group-concat mysql?

mysql group_concat separator

Question

¿Cómo crear formato json con group-concat mysql?

(Yo uso MySQL)

Ejemplo 1:

tabla 1:

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

Como código de sintaxis que no me da el formato:

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

salida que necesito:

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

Gracias

Answer 1

Con las versiones más recientes de MySQL, puede usar la función JSON_OBJECT para lograr el resultado deseado, así:

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

Para preparar la respuesta SQL para analizarla como una matriz:

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

Esto le dará una cadena como: [{name: ''ABC'', phone: ''111''}, {name: ''DEF'', phone: ''222''}] que puede ser analizado por JSON. Espero que esto ayude.

Answer 2

Intente esta consulta

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

Resultado del formato JSON -

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

Answer 3

La respuesta de Devart anterior es excelente, pero la pregunta de K2xL es válida. La respuesta que encontré fue codificar en hexadecimal la columna de nombre usando HEX (), lo que garantiza que creará JSON válido. Luego, en la aplicación, convierte el hexadecimal de nuevo en la cadena.

(Perdón por la autopromoción, pero) Escribí una pequeña publicación en el blog sobre esto con un poco más de detalle: http://www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in-mysql-using-group_concat/

[Editar para Oriol] Aquí hay un ejemplo:

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

También tenga en cuenta que he agregado un COALESCE en caso de que no haya elementos para ese correo electrónico.

Answer 4

Saliendo de la respuesta de @ Devart ... si el campo contiene saltos de línea o comillas dobles, el resultado no será JSON válido.

Entonces, si sabemos que el campo "teléfono" contiene comillas dobles y saltos de línea, nuestro SQL se vería así:

[{name:"Ben", phone:"655/"5333"},{name:"Joi", phone:"343/n4356"}]

Si Ben phone tiene una cita en medio de ella, y Joi''s tiene una nueva línea, el SQL daría (JSON válido) resultados como:

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

Answer 5

Usar asi

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

Aclamaciones