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?
4 Answers
I would approach this in a simple way.
- Unnest the two JSON structures using
JSON_TABLE(). - Join the two tables together.
- Construct the appropriate JSON objects and aggregate.
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.
Comments
You can extract by JSON_EXTRACT() function due to the index of each element within the arrays along with the contribution of row generation through use of a table from information_schema, then aggregate all results by using JSON_OBJECTAGG() returning from the subquery such as
SELECT JSON_OBJECTAGG(Js1,Js2)
FROM
(
SELECT JSON_UNQUOTE(JSON_EXTRACT(jsdata1,CONCAT('$[',@rn+1,']'))) AS Js1,
JSON_UNQUOTE(JSON_EXTRACT(jsdata2,CONCAT('$[',@rn+1,']'))) AS Js2,
@rn := @rn + 1 AS rn
FROM tab AS t1
JOIN (SELECT @rn:=-1) AS r
JOIN information_schema.tables AS t2
-- WHERE @rn < JSON_LENGTH(jsdata1) - 1 #redundant for MariaDB, but needed for MySQL
) AS j
where
'["a", "b", "c"]' is assumed to be the value of the column jsdata1 and
'["apple", "banana", "coconut"]' is assumed to be the value of the column jsdata2
within a table(tab) containing only one row inserted.
Comments
If you need to do it for every row in a table like this:
| id | jkeys | jvals |
|---|---|---|
| 1 | ["a", "b", "c"] | ["apple", "banana", "coconut"] |
| 2 | ["x", "y", "z"] | ["xigua", "yangmei", "zucchini"] |
Normalize the data using JSON_TABLE for both arrays and join them on the ordinality column.
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 |
Then you can group by the primary key (id) and collapse the key-value pairs into one object using JSON_OBJECTAGG():
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"} |
Comments
The basic way for it using JSON functions like:
select JSON_OBJECT(
JSON_UNQUOTE(JSON_EXTRACT(a, '$[0]')), JSON_EXTRACT(b, '$[0]'),
JSON_UNQUOTE(JSON_EXTRACT(a, '$[1]')), JSON_EXTRACT(b, '$[1]'),
JSON_UNQUOTE(JSON_EXTRACT(a, '$[2]')), JSON_EXTRACT(b, '$[2]')
) result from tbl;
1 Comment
Mahoor13
This code is not performing when I don't know the JSON array size.
JSON_TABLE()is simpler and better.