3 JSON Utility Functions in MySQL

MySQL provides us with a few utility functions that act on JSON values, or strings that can be parsed as JSON values.

Below are three JSON utility functions that we can use for such tasks.

The `JSON_PRETTY()` Function

The JSON_PRETTY() function formats JSON values for easier readability. In other words, it allows us to “prettify” our JSON documents so that they’re easier to read.

Example:

SET @data = '{"Person": {"Name": "Bart", "Age": 10, "Friends": ["Bart", "Milhouse"]}}';
SELECT JSON_PRETTY(@data);

Result:

+----------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(@data)                                                                                             |
+----------------------------------------------------------------------------------------------------------------+
| {
  "Person": {
    "Age": 10,
    "Name": "Bart",
    "Friends": [
      "Bart",
      "Milhouse"
    ]
  }
} |
+----------------------------------------------------------------------------------------------------------------+

Here, the JSON document that I passed to the function was condensed into a single line. But after passing it to the JSON_PRETTY() function, I got a nicely formatted version of the document, spread across multiple lines, with indentations, etc.

The `JSON_STORAGE_SIZE()` Function

The JSON_STORAGE_SIZE() function returns the number of bytes used to store the binary representation of a JSON document.

Example:

SELECT JSON_STORAGE_SIZE('{"a": 1}');

Result:

Here’s an example of getting the storage size of a JSON document that’s stored in a database column:

SELECT JSON_STORAGE_SIZE(Contents)
FROM Collections
WHERE CollectionId = 1;

Result:

We simply pass the column name to the function, and it returns the result.

The `JSON_STORAGE_FREE()` Function

The JSON_STORAGE_FREE() function shows how much storage space was freed up after a JSON column value was updated.

Suppose we run the following query:

SELECT Contents 
FROM Collections 
WHERE CollectionId = 4;

Which returns the following data:

+-------------------------------------+
| Contents                            |
+-------------------------------------+
| {"Name": "Homer", "Stupid": "True"} |
+-------------------------------------+

Now, let’s first use the JSON_STORAGE_FREE() function to see if any space has been freed up by any previous updates:

SELECT 
  JSON_STORAGE_SIZE(Contents) Size,
  JSON_STORAGE_FREE(Contents) Free
FROM Collections
WHERE CollectionId = 4;

Result:

+------+------+
| Size | Free |
+------+------+
|   40 |    0 |
+------+------+

Here, the JSON_STORAGE_SIZE() function tells us that the data uses up 40 bytes of storage space, and the JSON_STORAGE_FREE() function tells us that no space has been freed by any previous updates.

Now let’s update one of the JSON fields:

UPDATE Collections
SET Contents = JSON_SET(Contents, "$.Stupid", 1)
WHERE CollectionId = 4;

Result:

Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

And let’s run JSON_STORAGE_FREE() again to see how much space was freed up with that update:

SELECT 
  JSON_STORAGE_SIZE(Contents) Size,
  JSON_STORAGE_FREE(Contents) Free
FROM Collections
WHERE CollectionId = 4;

Result:

+------+------+
| Size | Free |
+------+------+
|   40 |    5 |
+------+------+

This result shows that a partial update of the JSON document occurred, and that this freed up 5 bytes of storage space. The result returned by JSON_STORAGE_SIZE() is unchanged by the partial update.

Partial updates are supported for updates using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). The direct assignment of a value to a JSON column cannot be partially updated, and therefore this will result in no freed space being reported.

The JSON_PRETTY() Function

The JSON_STORAGE_SIZE() Function

The JSON_STORAGE_FREE() Function

The `JSON_PRETTY()` Function

The `JSON_STORAGE_SIZE()` Function

The `JSON_STORAGE_FREE()` Function