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
– Akina, Commented Nov 26, 2021 at 8:25

Salman Arshad · Accepted Answer · 2021-11-29 10:05:15Z

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

answered Nov 29, 2021 at 10:05

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

1 Comment

dimButTries Over a year ago

Great work on providing examples for differing MySQL versions, I am kicking myself that I failed to use JSON_SET(). Appreciate your guidance!

Kiran Maniya · Accepted Answer · 2021-12-02 18:20:42Z

0

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.

edited Dec 2, 2021 at 18:20

answered Dec 2, 2021 at 18:15

Kiran Maniya

9,09911 gold badges63 silver badges87 bronze badges

Collectives™ on Stack Overflow

Partial update of JSON Object in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related