The MySQL JSON data type
By Mike Stojan |
Overview
JavaScript Object Notation (JSON) is a light-weight text-based file format similar to YAML or XML which simplifies data exchange. It was invented by Douglas Crockford in the early 2000s and became increasingly popular with the rise of document-based (also called NoSQL) databases.
JSON supports strings, numbers, booleans, objects, and arrays as well as null values. A simple JSON example containing key-value pairs, an object "bandMembers" and an array "songs" would look like this:
{
"artist": "Starlord Band",
"bandMembers": {
"vocals": "Steve Szczepkowski",
"guitar": "Yohann Boudreault",
"bass": "Yannick T.",
"drums": "Vince T."
},
"bandMembersCount": 4,
"album": "Space Rider",
"releaseDate": "2021-10-25",
"songs": [
"Zero to Hero",
"Space Riders with No Names",
"Ghost",
"Bit of Good (Bit of Bad)",
"Watch me shine",
"We’re Here",
"The Darkness inside",
"No Guts No Glory",
"All for One",
"Solar Skies"
],
"songsCount": 10
}
MySQL has implemented rudimentary support for the JSON data type with version 5.7.8 in mid 2015 and has been adding improvements and new features ever since. Seven years later, MySQL now supports numerous SQL functions to work with JSON documents, it provides automatic content validation, allows for partial in-place updates, and uses a binary storage format for increased performance.
When to use JSON
Relational databases follow a predetermined structure and put emphasis on data cohesion and integrity. To achieve this, its data types and formats, as well as its data size, are all being enforced rigorously by the means of a schema.
The JSON data type is a bit of an anti-pattern to the rigorousness nature of such a schema. It allows you to break out of it, to gain flexibility when you need it. And it proves useful as long as you're aware of the trade-offs described in the next section.
Some examples of when it may be beneficial to store data as a JSON document are:
- Log output written by an application or a server
- A Rest API response you want to store
- Storing configuration data
- A set of entities with variable attributes
You can also use JSON documents in your relational database design to break up complex relations spanning across multiple tables. This process is called denormalization, which is another relational database anti-pattern. In certain cases, however, it can result in performance improvements depending on your use case and application design.
Caveats
The flexibility provided by the JSON data type comes with a few caveats you will need to be aware of.
Most notably, you will need to factor in that JSON documents often require more storage capacity. In MySQL, their storage footprint is similar to the LONGBLOB or LONGTEXT data type. There is an overhead, though, due to the binary enconding and the added metadata and dictionaries which exist to speed up database reads. A good rule of thumb is that a string stored in JSON uses roughly 4 to 10 bytes of additional storage compared to a LONGBLOB or LONGTEXT column.
If you want to optimize your database schema towards storage efficiency, it is best to go with MySQL's more traditional data types (CHAR, VARCHAR, INT, and alike), as they are all more storage-efficient than JSON can likely ever be.
Another caveat to be aware of is the performance impact. Similar to other binary formats, JSON documents cannot be indexed directly. This, and the variable amount of data you can store in a JSON document, means that querying a JSON column often uses more buffer space and returns larger result sets, leading to more data exchange.
Note
While JSON documents cannot be indexed directly in MySQL, they can be indexed indirectly. Learn how in Indexing JSON in MYSQL.
While a JSON document stored in MySQL can be up to 1 GB, in theory, it is recommended to keep JSON documents to only a few MB in size. On PlanetScale, we support JSON documents up to 67 MB.
JSON functions
MySQL comes with a robust set of JSON functions enabling you to create, update, read, or validate your JSON documents. PlanetScale supports all of the JSON functions except JSON_TABLE.
Common operations
Let’s walk through a few examples together.
First, we create a table with an INTEGER and a JSON column.
CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);
An empty table needs data, so let’s use JSON_ARRAY to add some.
INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));
How do we know this is an array? Well, we can get its type by using JSON_TYPE.
SELECT JSON_TYPE(songs) FROM songs; +------------------+ | json_type(songs) | +------------------+ | ARRAY | +------------------+
If we want to extract an item from the array, we can do so with JSON_EXTRACT. In the below example, we extract the fourth element from the array.
blog-mysql-json/main> SELECT JSON_EXTRACT(songs, '$[3]') FROM songs; +-----------------------------+ | json_extract(songs, '$[3]') | +-----------------------------+ | "Ghost" | +-----------------------------+
We can also use ->, which is the operator equivalent for JSON_EXTRACT.
blog-mysql-json/main> SELECT songs->'$[3]' FROM songs; +-----------------+ | songs -> '$[3]' | +-----------------+ | "Ghost" | +-----------------+
If we need the unquoted result, we can use ->>, which is short for JSON_UNQUOTE(JSON_EXTRACT()).
blog-mysql-json/main> SELECT songs->>'$[3]' FROM songs; +------------------+ | songs ->> '$[3]' | +------------------+ | Ghost | +------------------+
If we need to add data to the JSON array, we can use JSON_ARRAY_APPEND or JSON_ARRAY_INSERT to update it.
UPDATE songs SET songs = JSON_ARRAY_APPEND(songs, '$', "One last song"); UPDATE songs SET songs = JSON_ARRAY_INSERT(songs, '$[0]', "First song");
For more information on how to use all the different JSON functions, please see MySQL's documentation for the JSON data type and the JSON Function reference.
Further learning
If you'd like to learn more about data types in MySQL, we have an article on the INT data type and one on the VARCHAR data type that you may find useful.
We also have short videos on the following data types: