2

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'}
user16461680
  • 35
  • 5

1 Answers1

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • JSON_OBJECTAGG() was added in MySQL 5.7.22. Are you using Amazon Aurora? They forked from MySQL 5.7 before JSON_OBJECTAGG() was added. – Bill Karwin Aug 25 '21 at 22:42
  • It's close, yes, thanks. Correct way is to use with GROUP BY and JSON_OBJECTAGG() UPDATE `table1` as `t1` SET `t1`.`data` = JSON_MERGE_PATCH(`t1`.`data`, ( SELECT JSON_OBJECTAGG(`key`, `value`) FROM `table2` GROUP BY `id`)); – user16461680 Aug 25 '21 at 22:56
  • In my real case I have more columns and I had some rows after SELECT JSON_OBJECTAGG. It has fixed by GROUP_BY or WHERE. Thanks a lot – user16461680 Aug 25 '21 at 23:02