MYSQL - append new array elements to JSON column-CodePudding

I have a table with json columns with default empty arrays [].

old table

id	myJson
A1	[1, 2]
A12	[]

I want the table updated to below.

id	myJson
A1	[1, 2, 321, 432]
A12	[222]

Tried - INSERT INTO table (id, myJson) VALUES ("A1", "[321, 432]"), ("A12", "[222]") ON DUPLICATE KEY UPDATE myJson = JSON_ARRAY_APPEND(myJson, "$", myJson)

Above query and other tried so far did not produce desirable result.

What query can i use to append new arrays to old as shown in the tables?

CodePudding user response：

What version of MySQL are you using?

One option is to use JSON_MERGE_PRESERVE or JSON_MERGE_PATCH (as needed):

INSERT INTO `table` (`id`, `myJson`)
VALUES ('A1', '[321, 432]'), ('A12', '[222]') AS `new`
  ON DUPLICATE KEY UPDATE
    `table`.`myJson` = JSON_MERGE_PRESERVE(`table`.`myJson`, `new`.`myJson`);

See dbfiddle.