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

You provide the JSON document as an argument.

When the argument is a JSON column, this function returns the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.

When the argument is a string, the function returns the amount of storage space in the JSON binary representation that is created by parsing the string as JSON and converting it to binary.

Syntax

The syntax goes like this:

JSON_STORAGE_SIZE(json_val)

Where json_val is the JSON document for which to return the storage size. This must be a valid JSON document or a string which can be parsed as one.

Example 1 – Basic Usage

Here’s an example to demonstrate.

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

Result:

+--------+
| Result |
+--------+
|     13 |
+--------+

In this case, the document size is 13 bytes.

Here’s a slightly larger JSON document.

SELECT JSON_STORAGE_SIZE('{"a": 1, "b": 2, "c": 3}') Result;

Result:

+--------+
| Result |
+--------+
|     29 |
+--------+

So this one is 29 bytes.

And here’s a larger one again.

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

Result:

+--------+
| Result |
+--------+
|     86 |
+--------+

Example 2 – Database Columns

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

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

Result:

+------+
| Size |
+------+
|  503 |
+------+

In this case, the database column was a JSON column and it contains the following data.

USE Music;
SELECT JSON_PRETTY(Contents) Contents
FROM Collections
WHERE CollectionId = 1;

Result:

[
  {
    "Albums": [
      {
        "AlbumName": "Powerage"
      }
    ],
    "ArtistName": "AC/DC"
  },
  {
    "Albums": [
      {
        "AlbumName": "Ziltoid the Omniscient"
      },
      {
        "AlbumName": "Casualties of Cool"
      },
      {
        "AlbumName": "Epicloud"
      }
    ],
    "ArtistName": "Devin Townsend"
  },
  {
    "Albums": [
      {
        "AlbumName": "Powerslave"
      },
      {
        "AlbumName": "Somewhere in Time"
      },
      {
        "AlbumName": "Piece of Mind"
      },
      {
        "AlbumName": "Killers"
      },
      {
        "AlbumName": "No Prayer for the Dying"
      }
    ],
    "ArtistName": "Iron Maiden"
  }
]

In this case, I used the JSON_PRETTY() function to make the results more readable.