I have two JSON array fields in MySQL like this:
["a", "b", "c"]
["apple", "banana", "coconut"]
Now I want to combine them into one JSON object like this:
{"a":"apple", "b":"banana", "c":"coconut"}
Is there any MySQL function for this?
I would approach this in a simple way.
JSON_TABLE()
.The following implements this logic. The first CTE extracts the keys. The second extracts the values, and finally these are combined:
WITH the_keys as (
SELECT j.*
FROM t CROSS JOIN
JSON_TABLE(t.jsdata1,
'$[*]'
columns (seqnum for ordinality, the_key varchar(255) path '$')
) j
),
the_values as (
SELECT j.*
FROM t CROSS JOIN
JSON_TABLE(t.jsdata2,
'$[*]'
columns (seqnum for ordinality, val varchar(255) path '$')
) j
)
select json_objectagg(the_keys.the_key, the_values.val)
from the_keys join
the_values
on the_keys.seqnum = the_values.seqnum;
Here is a db<>fiddle.
Note that this is quite generalizable (you can add more elements to the rows). You can readily adjust it to return multiple rows of data, if you you have key/value pairs on different rows, and it uses no deprecated functionality.