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

Provide CREATE TABLE + INSERT INTO with some initial sample data (3-5 rows). Specify needed actions. Show desired final data state after each separate action. Also provide precise MySQL version. — Akina, Nov 26 '21 at 08:25

score 1 · Accepted Answer · answered Nov 29 '21 at 10:05

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

Great work on providing examples for differing MySQL versions, I am kicking myself that I failed to use JSON_SET(). Appreciate your guidance! — dimButTries, Dec 15 '21 at 15:57

Kiran Maniya · Answer 2 · 2021-12-02T18:20:42.527

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.

Partial update of JSON Object in MySQL

2 Answers2

Linked