MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t(id int auto_increment primary key, jkeys json, jvals json);
insert into t (jkeys, jvals) values 
  ('["a", "b", "c"]', '["apple", "banana", "coconut"]'),
  ('["x", "y", "z"]', '["xigua", "yangmei", "zucchini"]');

Records: 2  Duplicates: 0  Warnings: 0


select t.id, cast(t.jkeys as char) as jkeys, cast(t.jvals as char) as jvals from t;


id
jkeys
jvals


1
["a", "b", "c"]
["apple", "banana", "coconut"]

2
["x", "y", "z"]
["xigua", "yangmei", "zucchini"]


select t.id, k.o, k.jkey, v.jval
from t
join json_table(t.jkeys, '$[*]' columns (o for ordinality, jkey text path '$')) k
join json_table(t.jvals, '$[*]' columns (o for ordinality, jval text path '$')) v
  using(o)


id
o
jkey
jval


1
1
a
apple

1
2
b
banana

1
3
c
coconut

2
1
x
xigua

2
2
y
yangmei

2
3
z
zucchini


select t.id, json_objectagg(k.jkey, v.jval) as j
from t
join json_table(t.jkeys, '$[*]' columns (o for ordinality, jkey text path '$')) k
join json_table(t.jvals, '$[*]' columns (o for ordinality, jval text path '$')) v
  using(o)
group by t.id


id
j


1
{"a": "apple", "b": "banana", "c": "coconut"}

2
{"x": "xigua", "y": "yangmei", "z": "zucchini"}

id	jkeys	jvals
1	["a", "b", "c"]	["apple", "banana", "coconut"]
2	["x", "y", "z"]	["xigua", "yangmei", "zucchini"]

id	j
1	{"a": "apple", "b": "banana", "c": "coconut"}
2	{"x": "xigua", "y": "yangmei", "z": "zucchini"}