get all unique json_keys in a json field in mariadb code example
Example 1: mariadb remove prop JSON
+----+--------+-------+-------+---------------------------------+
| id | name | price | stock | attr |
+----+--------+-------+-------+---------------------------------+
| 2 | Shirt | 10.50 | 78 | {"size": 42, "colour": "white"} |
| 3 | Blouse | 17.00 | 15 | {"colour": "white"} |
+----+--------+-------+-------+---------------------------------+
UPDATE
Clothes
SET
attr = JSON_REMOVE(attr, '$.size');
WHERE
id = 2;
+----+--------+-------+-------+---------------------------------+
| id | name | price | stock | attr |
+----+--------+-------+-------+---------------------------------+
| 2 | Shirt | 10.50 | 78 | {"colour": "white"} |
| 3 | Blouse | 17.00 | 15 | {"colour": "white"} |
+----+--------+-------+-------+---------------------------------+
Example 2: mariadb add prop to JSON
+----+--------+-------+-------+---------------------------------+
| id | name | price | stock | attr |
+----+--------+-------+-------+---------------------------------+
| 2 | Shirt | 10.50 | 78 | {"size": 42, "colour": "white"} |
| 3 | Blouse | 17.00 | 15 | {"colour": "white"} |
+----+--------+-------+-------+---------------------------------+
UPDATE
Clothes
SET
attr = JSON_INSERT(attr, '$.size', 40);
WHERE
id = 3;
+----+--------+-------+-------+---------------------------------+
| id | name | price | stock | attr |
+----+--------+-------+-------+---------------------------------+
| 2 | Shirt | 10.50 | 78 | {"size": 42, "colour": "white"} |
| 3 | Blouse | 17.00 | 15 | {"colour": "white", "size": 40} |
+----+--------+-------+-------+---------------------------------+