MySQL JSON_REMOVE with multi array

Question

I have the following JSON doc stored in MySQL 8 column called my_data:

{
  "managers": {
    "11": {
      "created": "2019-11-07"
    },
    "12": {
      "created": "2020-03-12"
    },
  }
}

How can I remove the "11" info.

UPDATE table SET my_data = JSON_REMOVE(my_data, ??) WHERE id = 1;

Answer 1

First of all, the data you show is not valid JSON. I think it should be the following:

{
  "managers": {
    "11": {
      "created": "2019-11-07"
    },
    "12": {
      "created": "2020-03-12"
    }
  }
}

I set a variable so I could test with it:

mysql> set @j = '...json above...';

I tried this, but it didn't like using an integer as a path key:

mysql> select json_remove(@j, '$.managers.11');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 13.

So I quoted the key as "11":

mysql> select json_remove(@j, '$.managers."11"');
+-------------------------------------------------+
| json_remove(@j, '$.managers."11"')              |
+-------------------------------------------------+
| {"managers": {"12": {"created": "2020-03-12"}}} |
+-------------------------------------------------+

---

But I would really do it this way:

DELETE FROM managers WHERE manager_id = 11;

That is, don't use JSON at all. Create a table for managers, with the 11 or 12 values as the primary key.

Using JSON in MySQL just makes it much harder to write code, and less efficient. This is what the Inner-Platform Effect antipattern is all about.