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!
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, '|', '", "'), '"]'))
;
id case_id tag
1 11 plane
2 12 car
3 12 plane
4 12 truck
P.S. This solution only for MySQL 8
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.