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"))
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"}