Partial update of JSON Object in MySQL

Question

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

Answer 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