CODEWITHSUNDEEP
mysqlarraysjsonsql-update
Dare
Dare

Reputation: 15

MYSQL - append new array elements to JSON column

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?

Upvotes: 0

Views: 591

Answers (1)

wchiquito
wchiquito

Reputation: 16569

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.

Upvotes: 1

Related Questions