Как создать json-формат с помощью group-concat mysql?

(я использую MySQL)

Пример1:

table1:

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

как синтаксический код, который не дает мне формат:

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

который мне нужен:

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

Спасибо

Ответ 1

Попробуйте этот запрос -

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

Результат формата JSON -

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

Ответ 2

В более новых версиях MySQL вы можете использовать функцию JSON_OBJECT для достижения желаемого результата, например:

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

Чтобы получить ответ SQL, готовый для анализа в виде массива:

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

Это даст вам строку типа: [{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}] которую можно проанализировать JSON. Надеюсь это поможет.

Ответ 3

Ответ Devart выше - это здорово, но вопрос K2xL действителен. Ответ, который я нашел, заключался в шестнадцатеричном кодировании столбца имени с помощью HEX(), который гарантирует, что он создаст действительный JSON. Затем в приложении преобразуйте шестнадцатеричный код обратно в строку.

(Извините за саморекламу, но) Я написал небольшое сообщение об этом в блоге с более подробной информацией: http://www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in- MySQL-помощь-GROUP_CONCAT/

[Править для Oriol] Вот пример:

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

Также обратите внимание, что я добавил COALESCE на случай, если для этого письма нет элементов.

Ответ 4

Отключение ответа @Devart... если поле содержит строки или двойные кавычки, результат не будет действителен JSON.

Итак, если мы знаем, что поле "телефон" иногда содержит двойные кавычки и разрывы строк, наш SQL будет выглядеть так:

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

Если у телефона Бен есть цитата в середине, и у Joi есть новая строка, SQL даст (действительные результаты JSON), например:

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

Ответ 5

Используйте это как

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

Приветствия

Ответ 6

Я надеюсь, что это находит правильные глаза.

Вы можете использовать (для массивов):

JSON_ARRAYAGG(col_or_expr) as ...

(для объектов)

JSON_OBJECTAGG(key, value) as ...