How to create new Records from Concatenated Field in MySQL Table

user_13984779 0
Viewed 51

I have a table 'Cases' with a field string field called 'Tags', which contains tags seperated by | character:

Cases

+----+-------------------+   
| Id |       Tags        |  
+----+-------------------+  
| 12 | "car|plane|truck" |  
| 11 | "plane"           |  
+----+-------------------+

and I would like to create a new Table to map these Tags as follow:

Tags

+----+---------+-------+  
| Id | case_id |  tag  |  
+----+---------+-------+  
|  1 |      12 | car   |  
|  2 |      12 | plane |  
|  3 |      12 | truck |  
|  4 |      11 | plane |  
+----+---------+-------+

How do I write an sql statement to create each record on the table as described above?

Thanks!

mysql sql
2 Answers

The solution is bit complicate:

set @id = 0;
select 
    @id:=(@id + 1) as id,
    id as case_id,
    JSON_UNQUOTE(
      JSON_EXTRACT(
        CONCAT('["', REPLACE(tags, '|', '", "'), '"]'), -- format string as JSIN array
        CONCAT('$[',pos,']')
      )
    ) tag
from Tags
join JSON_TABLE( -- join series pseudo table
    "[0,1,2,3]", -- this array length must be equal max tag elements length 
    "$[*]" 
    COLUMNS(pos varchar(255) PATH '$')
) as seq
    on seq.pos<JSON_LENGTH(CONCAT('["', REPLACE(tags, '|', '", "'), '"]'))
;

DB Fiddle example

id  case_id     tag
1   11          plane
2   12          car
3   12          plane
4   12          truck

P.S. This solution only for MySQL 8

user_33993560

I think a recursive CTE is a simplish way to solve this:

with recursive cte as (
      select id, concat(tag, '|') as rest, cast(null as char(255)) as tag, 1 as lev
      from tags
      union all
      select id, substr(rest, instr(rest, '|') + 1),
             substring_index(rest, '|', 1), lev + 1
      from cte
      where rest <> ''
     )
select id, tag
from cte
where lev > 1;

Here is a db<>fiddle.

user_11440350
Related