How to modify a JSON field in SQL ?

Let’s start with some info about how MySQL Document Store handles JSON documents.

Document Store and CRUD

We know that MySQL 8.0 Document Store handles JSON documents with CRUD operations. We can add, delete and modify those documents very easily:

 JS > db.mycollection.find()
{
    "_id": "0000624d3e890000000000000001",
    "name": "my_iot1",
    "type": "sensor",
    "capabilities": "{'temperature':'true','humidity':'true'}"
}
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot2",
    "type": "sensor",
    "capabilities": "{'temperature':'true'}"
}
2 documents in set (0.0007 sec)

To modify a document, the modify method can be used in different ways:

As illustrate above, we have:

• set()
• unset()
• patch()
• and arrays related methods

Let’s have a look at how to use them:

set

 JS > db.mycollection.modify("_id = '0000624d3e890000000000000002'").set("version", "0.1")
Query OK, 1 item affected (0.0031 sec)

 JS > db.mycollection.find("_id = '0000624d3e890000000000000002'")
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot2",
    "type": "sensor",
    "version": "0.1",
    "capabilities": "{'temperature':'true'}"
}
1 document in set (0.0008 sec)

unset

 JS > db.mycollection.modify("_id = '0000624d3e890000000000000002'").unset("version")
Query OK, 1 item affected (0.0030 sec)

 JS > db.mycollection.find("_id = '0000624d3e890000000000000002'")
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot2",
    "type": "sensor",
    "capabilities": "{'temperature':'true'}"
}
1 document in set (0.0005 sec)

patch

 JS > db.mycollection.modify("_id = '0000624d3e890000000000000002'").patch({"version": "0.2", "name": "my_iot3"})
Query OK, 1 item affected (0.0033 sec)

 JS > db.mycollection.find("_id = '0000624d3e890000000000000002'")
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot3",
    "type": "sensor",
    "version": "0.2",
    "capabilities": "{'temperature':'true'}"
}
1 document in set (0.0007 sec)

The patch() method is my favorite one as it requires a JSON entry and do the merge.

arrays

Let’s add a new attribute (allowed_users) that contains an array:

 JS > db.mycollection.modify("_id = '0000624d3e890000000000000002'").patch({"allowed_users": ["fred"]})
Query OK, 1 item affected (0.0032 sec)

 JS > db.mycollection.find("_id = '0000624d3e890000000000000002'")
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot3",
    "type": "sensor",
    "version": "0.2",
    "allowed_users": [
        "fred"
    ],
    "capabilities": "{'temperature':'true'}"
}
1 document in set (0.0005 sec)

And now let’s add one entry:

 JS > db.mycollection.modify("_id = '0000624d3e890000000000000002'").arrayAppend("allowed_users", "miguel")
Query OK, 1 item affected (0.0030 sec)

 JS > db.mycollection.find("_id = '0000624d3e890000000000000002'")
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot3",
    "type": "sensor",
    "version": "0.2",
    "allowed_user": [
        "fred",
        "miguel"
    ],
    "capabilities": "{'temperature':'true'}"
}
1 document in set (0.0007 sec)

And now to delete one element from the array, we need to provide its index:

JS > db.mycollection.modify("_id = '0000624d3e890000000000000002'").arrayDelete("allowed_user[1]")
Query OK, 1 item affected (0.0027 sec)

JS > db.mycollection.find("_id = '0000624d3e890000000000000002'")
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot3",
    "type": "sensor",
    "version": "0.2",
    "allowed_user": [
        "fred"
    ],
    "capabilities": "{'temperature':'true'}"
}
1 document in set (0.0006 sec)

We can see that the first element of an array is 0.

JSON in SQL

Now that we reviewed how JSON are handled with CRUD operation via the X Dev API, let’s see how JSON columns can also be modified in SQL.

Let’s use the following table:

CREATE TABLE `mytable` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `inserted` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `attributes` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

And we have 2 records:

SQL > select * from mytable;
+----+---------+---------------------+----------+--------------------+
| id | name    | inserted            | modified | attributes         |
+----+---------+---------------------+----------+--------------------+
|  1 | my_iot1 | 2022-04-06 09:48:54 | NULL     | {"type": "sensor"} |
|  2 | my_iot2 | 2022-04-06 09:49:06 | NULL     | {"type": "sensor"} |
+----+---------+---------------------+----------+--------------------+

To modify JSON fields in SQL, we need to use dedicated functions:

• JSON_SET()
• JSON_INSERT()
• JSON_REPLACE()
• JSON_MERGE_PRESERVE()
• JSON_MERGE_PATCH()
• JSON_REMOVE()
• JSON_ARRAY_APPEND() and JSON_ARRAY_INSERT()

Let’s see some examples, and start with adding the capabilities attribute to our JSON column for all our records in the table:

JSON_SET

SQL > update mytable set attributes=JSON_SET(attributes, "$.version", "0.1") ;
Query OK, 2 rows affected (0.0029 sec)

Compare to the CRUD operations, we nee to provide the JSON field’s name and the path ($.version).

SQL > select id, name, attributes from mytable;
+----+---------+--------------------------------------+
| id | name    | attributes                           |
+----+---------+--------------------------------------+
|  1 | my_iot1 | {"type": "sensor", "version": "0.1"} |
|  2 | my_iot2 | {"type": "sensor", "version": "0.1"} |
+----+---------+--------------------------------------+
2 rows in set (0.0007 sec)

JSON_INSERT and JSON_REPLACE

Those functions are almost identical, however if an attribute already exists, JSON_INSERT() won´t modify it:

SQL > update mytable set attributes=JSON_INSERT(attributes, "$.type", "new sensor") where id=2;
Query OK, 1 row affected (0.0012 sec)

SQL > select id, name, JSON_PRETTY(attributes) from mytable where id=2\G
*************************** 1. row ***************************
                     id: 2
                   name: my_iot2
JSON_PRETTY(attributes): {
  "type": "sensor",
  "version": "0.1"
}
1 row in set (0.0007 sec)

SQL > update mytable set attributes=JSON_REPLACE(attributes, "$.type", "new sensor") where id=2;
Query OK, 1 row affected (0.0030 sec)

SQL > select id, name, JSON_PRETTY(attributes) from mytable where id=2\G
*************************** 1. row ***************************
                     id: 2
                   name: my_iot2
JSON_PRETTY(attributes): {
  "type": "new sensor",
  "version": "0.1"
}
1 row in set (0.0006 sec)

JSON_MERGE_PATCH

If we want to add an embedded JSON entry like we did for the capabilities, JSON_SET won’t work easily in SQL and will mostly handle it as a string… so once again the patch version is most recommended one:

SQL > update mytable set attributes=JSON_MERGE_PATCH(attributes, '{"capabilities": {"humidity": "true", "temperature": "true"}}');
Query OK, 2 rows affected (0.0028 sec)

SQL > select id, name, json_pretty(attributes) attributes from mytable\G
*************************** 1. row ***************************
        id: 1
      name: my_iot1
attributes: {
  "type": "sensor",
  "version": "0.1",
  "capabilities": {
    "humidity": "true",
    "temperature": "true"
  }
}
*************************** 2. row ***************************
        id: 2
      name: my_iot2
attributes: {
  "type": "sensor",
  "version": "0.1",
  "capabilities": {
    "humidity": "true",
    "temperature": "true"
  }
}
2 rows in set (0.0010 sec)

Now if we want to remove the humidity capability, how can we do that ?

We just need to use again json_merge_patch() and set the value to null:

SQL > update mytable set attributes=JSON_MERGE_PATCH(attributes, '{"capabilities": {"humidity": null}}');
Query OK, 2 rows affected (0.0033 sec)

SQL > select id, name, json_pretty(attributes) attributes from mytable\G
*************************** 1. row ***************************
        id: 1
      name: my_iot1
attributes: {
  "type": "sensor",
  "version": "0.1",
  "capabilities": {
    "temperature": "true"
  }
}
*************************** 2. row ***************************
        id: 2
      name: my_iot2
attributes: {
  "type": "sensor",
  "version": "0.1",
  "capabilities": {
    "temperature": "true"
  }
}
2 rows in set (0.0007 sec)

JSON_MERGE_PRESERVE

This function allows to preserve the content already present for an attribute and create an array of the values:

SQL > update mytable set attributes=JSON_MERGE_PRESERVE(attributes, '{"version": "0.2"}');
Query OK, 2 rows affected (0.0029 sec)

SQL > select id, name, json_pretty(attributes) attributes from mytable\G
*************************** 1. row ***************************
        id: 1
      name: my_iot1
attributes: {
  "type": "sensor",
  "version": [
    "0.1",
    "0.2"
  ],
  "capabilities": {
    "temperature": "true"
  }
}
*************************** 2. row ***************************
        id: 2
      name: my_iot2
attributes: {
  "type": "sensor",
  "version": [
    "0.1",
    "0.2"
  ],
  "capabilities": {
    "temperature": "true"
  }
}
2 rows in set (0.0013 sec)

And this final example illustrates how we can deal with such entries by retrieving the last value:

SQL > select id, name, attributes->>"$.version[last]" version from mytable;
+----+---------+---------+
| id | name    | version |
+----+---------+---------+
|  1 | my_iot1 | 0.2     |
|  2 | my_iot2 | 0.2     |
+----+---------+---------+
2 rows in set (0.0008 sec)

Now you know a bit more how to use JSON datatype in MySQL with CRUD operations or in SQL.

Don’t forget that the X Dev API (X Protocol, to perform the CRUD operations) is also enabled by default in MySQL Database Service!

As usual, enjoy MySQL !