代码之家 › 专栏 › 技术社区 › Karma Blackshaw

MySQL解析并拆分JSON值

unnest knex.js json arrays mysql

Karma Blackshaw · 技术社区 · 5 年前

我有一列包含不同长度的JSON值

["The Cherries:2.50","Draw:3.25","Swansea Jacks:2.87"]

我希望将它们拆分并存储为JSON,如下所示:

[
  {
    name: "The Cherries",
    odds: 2.50
  },
  {
    name: "Draw",
    odds: 3.25
  },
  {
    name: "Swansea",
    odds: 2.87
  },
]

1 回复 | 直到 5 年前

GMB 5 年前

如果您运行的是MySLQ 8.0,则可以使用 json_table() 将原始阵列拆分为行,然后生成新对象并使用 json_arrayagg() .

id :

select 
    t.id, 
    json_arrayagg(json_object(
        'name', substring(j.val, 1, locate(':', j.val) - 1), 
        'odds', substring(j.val, locate(':', j.val) + 1)
    )) new_js
from mytable t
cross join json_table(t.js, '$[*]' columns (val varchar(500) path '$')) as j
group by t.id

Demo on DB Fiddle

样本数据:

id | js                                                      
-: | :-------------------------------------------------------
 1 | ["The Cherries:2.50", "Draw:3.25", "Swansea Jacks:2.87"]

id | new_js                                                                                                                 
-: | :----------------------------------------------------------------------------------------------------------------------
 1 | [{"name": "The Cherries", "odds": "2.50"}, {"name": "Draw", "odds": "3.25"}, {"name": "Swansea Jacks", "odds": "2.87"}]

Riddhijain 5 年前

可以使用json_table从json对象创建行。只需将table_name替换为表名,将json替换为包含json的列

 SELECT json_arrayagg(json_object('name',SUBSTRING_INDEX(person, ':', 1) ,'odds',SUBSTRING_INDEX(person, ':', -1) )) 
 FROM table_name,
 JSON_TABLE(json, '$[*]' COLUMNS (person VARCHAR(40) PATH '$') people;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=801de9f067e89a48d45ef9a5bd2d094a