Как обновить json-поле с данными из подзапроса?

Question

Есть таблица table1 с json-колонкой data

SELECT id,data FROM table1;

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

Я могу использовать:

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

И получу:

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

Но как я могу обновить данные из подзапроса, используя JSON_MERGE_PATCH? Например, из table2:

id	key	value
1	'key10'	'value10'
2	'key13'	'value13'

Я пробовал "SELECT key, value FROM table2" с JSON_ARRAY и т.д. в JSON_MERGE_PATCH, но не нашёл корректного варианта.

Ожидаемый результат:

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

Answer 1

UPDATE table1 
JOIN table2 USING (id)
SET table1.data = JSON_MERGE_PATCH(table1.data, JSON_OBJECT(table2.`key`, table2.value));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f6c9224a1b0258d4423cbc099c3bffe8

Но это же добавит только по одному значению. А нужно добавить все пары значений из table2. В table2 может быть и больше записей. – WD_KMS

Значит, вместо table2 пишете подзапрос, который объединяет все записи таблицы в один JSON, и используете CROSS JOIN.

UPDATE table1 
CROSS JOIN ( SELECT JSON_OBJECTAGG(table2.`key`, table2.value) data
             FROM table2 ) table3
SET table1.data = JSON_MERGE_PATCH(table1.data, table3.data);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e6253c49766897a272c154ab1f91227c

Answer 2

Решение

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

БЛОГ НА HUSL

Как обновить json-поле с данными из подзапроса?

Ответы (2 шт):