Currently I have in my DB(mariaDB 10.3) a column that is called data and contains a json array:
client| data
1 | '["a","b","c"]'
2 | '["k"]'
and I would like to brake it down into
client| data
1 | "a"
1 | "b"
1 | "c"
2 | "k"
Unfortunately, MariaDB does not support "unnesting" function JSON_TABLE(), unlike MySQL 8.0.
We are left with some kind of iterative approach, typicaly by using a table of numbers to enumerate the array elements. If you have a table with at least as many rows as the maximum number of elements in an array, say bigtable, you can do:
select client, json_unquote(json_extract(t.data, concat('$[', n.rn - 1, ']'))) value
from mytable t
inner join (select row_number() over() rn from bigtable) n
on n.rn <= json_length(t.data)
order by t.client, n.rn
client | value
-----: | :----
1 | a
1 | b
1 | c
2 | k