sql - Combine two json array as key-value in mysql and create one json object

Question

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?

question from:https://stackoverflow.com/questions/65642272/combine-two-json-array-as-key-value-in-mysql-and-create-one-json-object

Answer 1

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.