How do I update a json column with a subquery?

Question

I have MySQL 5.7 and table1 with json column data.

SELECT id, data 
FROM table1;

id	data
1	{'key3': 'value3'}
2	{'key5': 'value5'}

I can use:

UPDATE `table1` as `t1`
SET `t1`.`data` =  JSON_MERGE_PATCH(`t1`.`data`, JSON_OBJECT('key1', 'value1', 'key2', 'value2'));

I will get:

id	data
1	{'key3': 'value3', 'key1': 'value1', 'key2': 'value2'}
2	{'key5': 'value5', 'key1': 'value1', 'key2': 'value2'}

How can I get data from a subquery and make JSON_MERGE_PATCH with that?

From table2:

id	key	value
1	'key10'	'value10'
2	'key13'	'value13'
3	'key100'	'value100'

I tried to use

SELECT key, value FROM table2

with JSON_ARRAY etc into JSON_MERGE_PATCH, but it is not correct.

Subquery returns rows from the table2 in the "key, value"-structure.

Expected data:

id	data
1	{'key3': 'value3', 'key10': 'value10', 'key13': 'value13', 'key100': 'value100'}
2	{'key5': 'value5', 'key10': 'value10', 'key13': 'value13', 'key100': 'value100'}

Answer 1

Something like this:

Edit: I misspelled JSON_OBJECTAGG() as JSON_OBJECT_AGG() in my previous answer. I have fixed it as JSON_OBJECTAGG():

UPDATE `table1` as `t1`
SET `t1`.`data` =  JSON_MERGE_PATCH(`t1`.`data`, 
    (SELECT JSON_OBJECTAGG(`key`, value) FROM table2));

Tested in MySQL 5.7.34. Note you must delimit the column name key in back-ticks because it's a reserved keyword.

Result after running the update:

mysql> select * from table1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"key3": "value3", "key10": "value10", "key13": "value13", "key100": "value100"} |
|  2 | {"key5": "value5", "key10": "value10", "key13": "value13", "key100": "value100"} |
+----+----------------------------------------------------------------------------------+

See https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-objectagg for details on the JSON_OBJECT_AGG() function.