如何将MySQL JSON数组转换为逗号分隔的字符串 [英] How to convert MySQL JSON array to comma separated string

问题描述

我在列中有以下电话号码:

I have the following phone numbers in a column:

["[phone]", "[phone]", "[phone]", "[phone]"]

我如何获得这样的信息:

How can I get that info like this:

[phone],[phone],[phone],[phone]

推荐答案

我认为这是最干净的MySQL清理方法，至少适用于8以下的MySQL版本

i think this is the most only MySQL clean way, atleast for MySQL versions under 8

查询

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT
  GROUP_CONCAT(
     JSON_UNQUOTE(
       JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
     )
  )                    

FROM (

  SELECT 
   @row := @row + 1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2
  CROSS JOIN (
    SELECT @row := -1 
  ) init_user_params 
) AS number_generator
CROSS JOIN (


SELECT 
    json
  , JSON_LENGTH(records.json) AS json_array_length    
FROM (

  SELECT 
   '["[phone]", "[phone]", "[phone]", "[phone]"]' AS json
  FROM 
   DUAL  
) AS records

) AS records 
WHERE
    number BETWEEN 0 AND  json_array_length - 1 

结果

| GROUP_CONCAT(
     JSON_UNQUOTE(
       JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
     )
  ) |
| -------------------------------------------------------------------------------------------------------------------------- |
| [phone],[phone],[phone],[phone]                                                                |

请参见演示

您听说过JSON_TABLE()吗? – oysteing

Have you heard of JSON_TABLE()? – oysteing

我有，我不认为每个人都已经在MySQL 8上，但是我也出于完整性考虑添加了它.

I have, i dont assume everybody to be on MySQL 8 already but i added it for completeness also.

仅适用于MySQL 8.0查询

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
 GROUP_CONCAT(item)
FROM JSON_TABLE(
     '["[phone]", "[phone]", "[phone]", "[phone]"]'
   , "$[*]"

   COLUMNS (
       rowid FOR ORDINALITY
     , item VARCHAR(100) PATH "$"   
   )
) AS json_parsed  

结果

| GROUP_CONCAT(item)                                          |
| ----------------------------------------------------------- |
| [phone],[phone],[phone],[phone] |

请参见演示

REPLACE()嵌套方法比较麻烦，但是应该在所有MySQL版本上都可以使用.

The REPLACE() nesting method is more messy, but should work on all MySQL versions.

SELECT 
 REPLACE(
   REPLACE(
      REPLACE(
       '["[phone]", "[phone]", "[phone]", "[phone]"]'
       , '['
       , ''
     )
     , ']'
     , ''
   )
   , '"'
   , ''
 )

结果

| REPLACE(
   REPLACE(
      REPLACE(
       '["[phone]", "[phone]", "[phone]", "[phone]"]'
       , '['
       , ''
     )
     , ']'
     , ''
   )
   , '"'
   , ''
 ) |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [phone], [phone], [phone], [phone]                                                                                                                                      |

请参见演示

这篇关于如何将MySQL JSON数组转换为逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！