sql—如何使用组concat mysql创建json格式?

8cdiaqws  于 2021-06-17  发布在  Mysql
关注(0)|答案(8)|浏览(355)

如何使用组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"}

谢谢

bfhwhh0e

bfhwhh0e1#

尝试此查询-

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

mrzz3bfm2#

上面devart的回答很好,但k2xl的问题是有效的。我找到的答案是使用hex()对name列进行十六进制编码,这确保了它将创建有效的json。然后在应用程序中,将十六进制转换回字符串。
(很抱歉自我推销,但是)我写了一篇关于这个的博文,其中有一些更详细的内容:http://www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in-mysql-using-group_concat/
[edit for 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,以防该电子邮件没有条目。

ars1skjm

ars1skjm3#

对于较新版本的mysql,您可以使用json对象函数来实现所需的结果,如下所示:

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解析。希望这有帮助。

tkclm6bt

tkclm6bt4#

与上面madacol的答案相似,但略有不同。除了jsonarrayagg,您还可以转换为json:

SELECT
        email,
       CAST( CONCAT(
        '[', 
           GROUP_CONCAT(
           JSON_OBJECT(
              'name', name,
              'phone', phone
            )
        ),']') AS JSON )
    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}] |
+---------------+-------------------------------------------------------------------+
c6ubokkw

c6ubokkw5#

偏离了@devart的答案。。。如果字段包含换行符或双引号,则结果将不是有效的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's有一个换行符,那么sql将给出如下(有效的json)结果:

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

r7s23pms6#

对于MySQL5.7.22+

SELECT
        email,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', name,
                'phone', 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}] |
+---------------+-------------------------------------------------------------------+

唯一的区别是那一栏 list 现在json是有效的,所以可以直接解析为json

crcmnpdw

crcmnpdw7#

我希望这是对的。
您可以使用:
对于阵列(文档):

JSON_ARRAYAGG(col_or_expr) as ...

对于对象(文档):

JSON_OBJECTAGG(key, value) as ...
wydwbb8l

wydwbb8l8#

像这样使用

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

干杯

相关问题