Update JSON data type column in MySql table

Question

I have started using MySQL 8 and trying to update JSON data type in a mysql table

My table t1 looks as below:

# id    group   names   
1100000 group1  [{"name": "name1", "type": "user"}, {"name": "name2", "type": "user"}, {"name": "techDept", "type": "dept"}]

I want to add user3 to the group1 and written below query:

update t1 set names = JSON_SET(names, "$.name", JSON_ARRAY('user3')) where group = 'group1';

However, the above query is not working

Provide sample data as CREATE TABLE + INSERT INTO. Show desired final data state. — Akina, Mar 18 '21 at 17:37

score 2 · Accepted Answer · answered Mar 18 '21 at 20:24

I suppose you want the result to be:

[{"name": "name1", "type": "user"}, {"name": "name2", "type": "user"}, {"name": "techDept", "type": "dept"}, {"name": "user3", "type": "user"}]

This should work:

UPDATE t1 SET names = JSON_ARRAY_APPEND(names, '$', JSON_OBJECT('name', 'user3', 'type', 'user'))
WHERE `group` = 'group1';

But it's not clear why you are using JSON at all. The normal way to store this data would be to create a second table for group members:

CREATE TABLE group_members (
  member_id INT PRIMARY KEY,
  `group` VARCHAR(10) NOT NULL,
  member_type ENUM('user','dept') NOT NULL DEFAULT 'user',
  name VARCHAR(10) NOT NULL
);

Then store one per row.

Adding a new member would be like:

INSERT INTO group_members
SET `group` = 'group1', name = 'user3';

So much simpler than using JSON!

Update JSON data type column in MySql table

1 Answers1