如何使用group-concat mysql创建json格式?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (5)
  • 关注 (0)
  • 查看 (176)

如何使用group-concat mysql创建json格式?

(我使用MySQL)

例1:

表格1:

email            |    name  |   phone
-------------------------------------
my1@gmail.com    | Ben      | 6555333
my2@gmail.com    | Tom      | 2322452
my2@gmail.com    | Dan      | 8768768
my1@gmail.com    | Joi      | 3434356

像语法代码,不给我的格式:

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

我需要的输出:

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

谢谢

提问于
用户回答回答于

如果字段包含换行或双引号,则结果将不是有效的JSON。

因此,如果我们知道“phone”字段偶尔包含双引号和换行,那么我们的SQL应该是:

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

如果Ben Phone在中间有一个引号,而Joi有一个换行符,SQL将给出(有效的JSON)结果,如下所示:

[{name:"Ben", phone:"655\"5333"},{name:"Joi", phone:"343\n4356"}]
用户回答回答于

像这样使用

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

使用HEX()对名称列进行十六进制编码,从而确保它将创建有效的JSON。然后在应用程序中,将十六进制转换回字符串。

[编辑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。

用户回答回答于

对于MySQL的新版本,您可以使用JSON[医]对象函数以实现所需的结果,如下所示:

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

希望能帮上忙。

用户回答回答于

试试这个查询-

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

JSON格式结果-

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

扫码关注云+社区

领取腾讯云代金券