Search code examples
mysqljsonmysql-json

Update JSON data type column in MySql table

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

Solution

  • 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!