'Partial update of JSON Object in MySQL

Good afternoon,

When I try to update part of a JSON Object, using ON DUPLICATE KEY UPDATE, how do you update a specific value with a key?

The code executes successfully but all values are updated when I just want the stock to change on update.

Any help would be welcomed, I am not convinced, I understand the MySQL JSON Path syntax, or perhaps JSON_SET cannot achieve my goal?

INSERT INTO table (name, attributes) VALUES
("Sarah", JSON_OBJECT('profile', "F", "el", "[4, 5, 6]")),
("John",  JSON_OBJECT('profile', "M", "el", "[10]"))
AS t
ON DUPLICATE KEY UPDATE
  attributes = JSON_SET(t.attributes, '$.attributes.el',  '$.attributes.el')
                                                         # ^
                                                         # +--- value being inserted

I have also tried another flavour without success:

attributes = JSON_REPLACE(t.attributes, '$.t.el', "$.t.el")

Third attempt using wildcards and json extract, which replaces the entire JSON_OBJECT()

attributes = JSON_REPLACE(t.attributes, '$.t[2]', JSON_EXTRACT(t.attributes, "$.stock"))
mysqlsqljsonmysql-json


Solution 1:[1]

If I understand correctly, you just need to use the VALUES function inside INSERT ... ON DUPLICATE KEY UPDATE statement which gives you access to the value being inserted:

CREATE TABLE t(
  name varchar(100) NOT NULL UNIQUE,
  attributes JSON
);

INSERT INTO t(name, attributes) VALUES
('Sarah', '{"profile": "F", "el": ["insrted", 1]}'),
('John',  '{"profile": "M", "el": ["insrted", 2]}');

-- insert + on duplicate (mysql 5.x)
INSERT INTO t(name, attributes) VALUES
('Sarah', '{"profile": "F", "el": ["dup_upd", 3]}'),
('John',  '{"profile": "M", "el": ["dup_upd", 4]}'),
('Jack',  '{"profile": "M", "el": ["insrted", 1]}')
ON DUPLICATE KEY UPDATE attributes =
    JSON_SET(attributes, '$.el', JSON_EXTRACT(VALUES(attributes), '$.el'));

-- insert + on duplicate (mysql 8.x)
INSERT INTO t(name, attributes) VALUES
('Sarah', '{"profile": "F", "el": ["dup_upd", 3]}'),
('John',  '{"profile": "M", "el": ["dup_upd", 4]}'),
('Jack',  '{"profile": "M", "el": ["insrted", 1]}')
AS t_ins
ON DUPLICATE KEY UPDATE attributes =
    JSON_SET(t.attributes, '$.el', JSON_EXTRACT(t_ins.attributes, '$.el'));

SELECT name, JSON_PRETTY(attributes)
FROM t

name  | JSON_PRETTY(attributes)
------|-------------------------------------------
Sarah | {"el": ["dup_upd", 3], "profile": "F"}
John  | {"el": ["dup_upd", 4], "profile": "M"}
Jack  | {"el": ["insrted", 1], "profile": "M"}

Demo on DB<>Fiddle

Solution 2:[2]

the JSON_EXTRACT might help you extract the new value from the duplicate row being inserted.

INSERT INTO table_name (name, attributes) VALUES
  ("Sarah", JSON_OBJECT('profile', "F", "el",  "[4, 5, 6]" )),
  ("John", JSON_OBJECT('profile', "M", "el",  "[10]" ))
AS t ON DUPLICATE KEY UPDATE
attributes = JSON_SET(table_name.attributes, '$.el', JSON_EXTRACT(t.attributes, '$.el'))

Note Aliasing the row being inserted using the AS keyword is only supported in MySQL version 8.x, In prior version you can refer to it using VALUES keyword.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Salman A
Solution 2

Related Questions