Jak utworzyć format json z group-concat mysql?

Jak utworzyć format json z group-concat mysql?

(używam MySQL)

Przykład1:

Table1: 

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

Jak kod składniowy, który nie daje mi formatu:

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

Wyjście, którego potrzebuję: 

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

Dzięki

Author: Yosef, 2012-09-20
Source

5 answers

Spróbuj tego zapytania - 

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

Wynik formatu JSON- 

+---------------+-------------------------------------------------------------+
| email         | list                                                        |
+---------------+-------------------------------------------------------------+
| [email protected] | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |
| [email protected] | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |
+---------------+-------------------------------------------------------------+
 46
Author: Devart,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-20 12:25:29

W nowszych wersjach MySQL, możesz użyć funkcji JSON_OBJECT, aby osiągnąć pożądany rezultat, jak tak: 

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

Aby uzyskać odpowiedź SQL gotową do przetworzenia w postaci tablicy: 

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

To da ci ciąg znaków taki jak: [{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}], który może być parsowany przez JSON. Mam nadzieję, że to pomoże.

 32
Author: myusuf,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-09-23 09:53:20

ODPOWIEDŹ Devarta powyżej jest świetna, ale pytanie K2xL jest poprawne. Odpowiedź, którą znalazłem, to kodowanie szesnastkowe kolumny nazwy za pomocą hex (), co zapewnia, że utworzy poprawny JSON. Następnie w aplikacji przekonwertować szesnastkowy z powrotem na łańcuch znaków.

(przepraszam za autopromocję, ale) napisałem o tym mały wpis na blogu z nieco bardziej szczegółowym: http://www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in-mysql-using-group_concat/

[edytuj dla Oriol] oto przykład: 

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

Również zauważ, że dodałem KOALESCE w przypadku, gdy nie ma elementów dla tego e-maila.

 12
Author: alexkorn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-05 18:16:20

[[2]] wychodząc z @Devart ' s answer... jeśli pole zawiera znaki liniowe lub podwójne cudzysłowy, wynik nie będzie poprawny JSON.

Tak więc, jeśli wiemy, że pole" telefon " czasami zawiera podwójne cudzysłowy i linebreaks, nasz SQL wyglądałby następująco: 

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

Jeśli Ben phone ma cytat w środku, a Joi ma nową linię, SQL da (poprawny JSON) wyniki takie jak: 

[{name:"Ben", phone:"655\"5333"},{name:"Joi", phone:"343\n4356"}]
 1
Author: Jonathan Harford,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-24 21:41:31

Użyj tak 

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

Cheers

 0
Author: Sundar G,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-20 12:06:50