By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (id int auto_increment primary key, j json)
;
insert into test (j) values
('[{"substanceId": 182, "text": "substance_name_182"}, {"substanceId": 183, "text": "substance_name_183"}]'),
('[{"substanceId": 184, "text": "substance_name_184_old"}]')
SELECT JSON_PRETTY(j)
FROM test
| JSON_PRETTY(j) |
|---|
| [ { "text": "substance_name_182", "substanceId": 182 }, { "text": "substance_name_183", "substanceId": 183 } ] |
| [ { "text": "substance_name_184_old", "substanceId": 184 } ] |
WITH upd AS (
SELECT *
FROM JSON_TABLE('[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]',
'$[*]' COLUMNS (
substanceId INT PATH '$.substanceId',
txt VARCHAR (100) PATH '$.text')
) jt
CROSS JOIN (SELECT DISTINCT id
FROM test) t
),
cur AS (
SELECT id, substanceId, txt
FROM test
JOIN JSON_TABLE(test.j,
'$[*]' COLUMNS (
substanceId INT PATH '$.substanceId',
txt VARCHAR (100) PATH '$.text')
) jt
),
allv AS (
SELECT COALESCE(upd.id, cur.id) AS id,
COALESCE(upd.substanceId, cur.substanceId) AS substanceId,
COALESCE(upd.txt, cur.txt) AS txt
FROM upd
LEFT JOIN cur ON cur.substanceId = upd.substanceId
UNION ALL
SELECT COALESCE(upd.id, cur.id) AS id,
COALESCE(upd.substanceId, cur.substanceId) AS substanceId,
COALESCE(upd.txt, cur.txt) AS txt
FROM upd
RIGHT JOIN cur ON cur.substanceId = upd.substanceId
),
obj AS (
SELECT DISTINCT id, JSON_OBJECT('substanceId', substanceId, 'text', txt) AS o
FROM allv
),
SELECT JSON_PRETTY(j)
FROM test
| JSON_PRETTY(j) |
|---|
| [ { "text": "substance_name_184", "substanceId": 184 }, { "text": "substance_name_182_new", "substanceId": 182 }, { "text": "substance_name_183", "substanceId": 183 } ] |
| [ { "text": "substance_name_184", "substanceId": 184 }, { "text": "substance_name_182_new", "substanceId": 182 } ] |