MYSQL - append new array elements to JSON column

Question

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?

Does this answer your question? MySQL append json object to array of json objects — Javier G.Raya
– Javier G.Raya, Commented Mar 31, 2022 at 7:55

wchiquito · Accepted Answer · 2022-03-31 09:49:26Z

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.

answered Mar 31, 2022 at 9:49

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dare Over a year ago

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)

wchiquito Over a year ago

@Dare: Are you executing the statement from the MySQL command line (CLI) or another client (PHPMyAdmin, for example)?

Dare Over a year ago

Executing from PHPMyAdmin, using XAMPP

Dare Over a year ago

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)) `

wchiquito Over a year ago

@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.

Collectives™ on Stack Overflow

MYSQL - append new array elements to JSON column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related