0

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?

Dare
  • 15
  • 4
  • Does this answer your question? [MySQL append json object to array of json objects](https://stackoverflow.com/questions/45674489/mysql-append-json-object-to-array-of-json-objects) – Javier G.Raya Mar 31 '22 at 07:55
  • See if this helps https://stackoverflow.com/questions/70083619/partial-update-of-json-object-in-mysql – Salman A Mar 31 '22 at 10:25

1 Answers1

1

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.

wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • Thanks. I Use version 8.0.28. It is producing error as I can't give alias to VALUES. `Unexpected token. (near "AS" at position 94) Unrecognized statement type. (near "AS" at position 94)` – Dare Mar 31 '22 at 12:48
  • @Dare: Are you executing the statement from the MySQL command line (CLI) or another client (PHPMyAdmin, for example)? – wchiquito Apr 01 '22 at 06:06
  • Executing from PHPMyAdmin, using XAMPP – Dare Apr 01 '22 at 09:41
  • This eventually worked on my PHPMyAdmin ` INSERT INTO table (id, myJson) VALUES ("A1", "[A321, 432]"), ("A12", "[222]") ON DUPLICATE KEY UPDATE myJson = JSON_MERGE_PRESERVE(myJson, VALUES(myJson)) ` – Dare Apr 01 '22 at 10:37
  • @Dare: Possibly PHPMyAdmin has not yet implemented the change and therefore the error. See [MySQL 8.0.20 - INSERT ... ON DUPLICATE KEY UPDATE statements is now deprecated](https://github.com/phpmyadmin/phpmyadmin/issues/16167). – wchiquito Apr 01 '22 at 11:58