I am sitting with a legacy database and im trying to create this table in mysql using json strings stored in the database.
I have a table that stores a massive json string and one of the keys inside that json has a json string as follows:
data->"$.json" = {
"col_a": [
"a_1",
"a_2",
"a_3",
"a_4"
],
"col_b": [
"b_1",
"b_2",
"b_3",
"b_4"
],
"col_c": [
"c_1",
"c_2",
"c_3",
"c_4"
]
}
and I need this output in mysql:
+-------+-------+-------+
| col_a | col_b | col_c |
+-------+-------+-------+
| a_1 | b_1 | c_1 |
| a_2 | b_2 | c_2 |
| a_3 | b_3 | c_3 |
| a_4 | b_4 | c_4 |
+-------+-------+-------+
So far I have managed to get the expected result by "indexing" multiple reads with ordinality and the joining on that index.
drop table if exists table1;
create table table1 (
id int primary key auto_increment,
data json
);
insert into table1 (data) values (
'{
"other1":{
"col_a": ["a_1", "a_2", "a_3", "a_4"],
"col_b": ["b_1", "b_2", "b_3", "b_4"],
"col_c": ["c_1", "c_2", "c_3", "c_4"]
},
"json": {
"col_a": ["a_1", "a_2", "a_3", "a_4"],
"col_b": ["b_1", "b_2", "b_3", "b_4"],
"col_c": ["c_1", "c_2", "c_3", "c_4"]
},
"other2":{
"col_a": ["a_1", "a_2", "a_3", "a_4"],
"col_b": ["b_1", "b_2", "b_3", "b_4"],
"col_c": ["c_1", "c_2", "c_3", "c_4"]
}
}'
);
select
a.col_a,
b.col_b,
c.col_c
from
table1 t
inner join
json_table(
t.data->'$.json',
'$.col_a[*]'
columns (
row_id for ordinality,
col_a varchar(10) path '$'
)
) a
inner join
json_table(
t.data->'$.json',
'$.col_b[*]'
columns (
row_id for ordinality,
col_b varchar(10) path '$'
)
) b
on a.row_id = b.row_id
inner join
json_table(
t.data->'$.json',
'$.col_c[*]'
columns (
row_id for ordinality,
col_c varchar(10) path '$'
)
) c
on a.row_id = c.row_id;
But I am wondering if there isn't a more efficient way of doing it where I dont have to join each column manually.
Thanks in advance!
col_a
in the JSON becomecol_1
in the output? And why doesa_1
become1_a
?'$.*[*]'
then you'd obtain all values in one column. If you'd use multiple NESTED PATH then you'd receive rows with the value in one column and NULLs with another ones. See dbfiddle.uk/gmDd-TNs