intro
Let’s explore everything you need to know about the JSON_EXTRACT MySQL function, a powerful tool for retrieving data from JSON documents.
Modern databases like MySQL can not only store JSON data, but also let you query and programmatically access fields within it. In detail, MySQL provides several JSON search functions. Well, the JSON_EXTRACT MySQL function is one of the most powerful!
In this guide, you will learn what JSON_EXTRACT is, how it works, and what kind of data it allows you to retrieve.
Let’s dive in!
What Is the JSON_EXTRACT MySQL Function?
In MySQL, the JSON_EXTRACT function retrieves data from a JSON document by selecting the parts that match a given JSON path expression. (In case you are not familiar with that syntax, we will dig into the JSON path syntax later.)
Note that the definition mentions “parts” in the plural. That is because JSON_EXTRACT can return multiple fields from the JSON document, depending on the JSON path you provide.
MySQL JSON_EXTRACT: Syntax and Usage
This is the syntax of the MySQL JSON_EXTRACT function:
1
JSON_EXTRACT(json_doc, path[, path] ...)
Where:
If the path list contains only a single JSON path expression, JSON_EXTRACT returns a single JSON value. For example:
1
SELECT JSON_EXTRACT('{"user": {"name": "Alice", "age": 30}}', '$.user.name');
In this case, the result will be:
1
"Alice"
Note: Those double quotes are there for a reason. JSON_EXTRACT returns a value of type JSON. So even if it looks like a regular string (and your client may display it as one), the returned data is always in valid JSON format.
If there are multiple path expressions, the MySQL JSON_EXTRACT function returns a JSON array of values, like in this example:
1
SELECT JSON_EXTRACT('{"user": {"name": "Alice", "age": 30}}', '$.user.name', '$.user.age');
This time, the result will be:
1
["Alice", 30]
Notes:
MySQL JSON Path Syntax
If you already know what MySQL JSON Path is and how it works, feel free to skip this section.
Many MySQL JSON functions—including JSON_EXTRACT—require a JSON path expression to locate specific elements within a JSON document. That expression must be written following the MySQL JSON path specification.
A JSON path consists of a scope, followed by one or more path legs. Here are the key rules for writing JSON path expressions in MySQL:
MySQL also supports the following wildcards:
For example, consider this JSON document:
1
{
2
"user": {
3
"name": "Alice",
4
"age": 30,
5
"emails": ["alice@example.com", "a.smith@example.com"],
6
"address": {
7
"city": "New York",
8
"zip": "10001"
9
}
10
}
11
}
This is what different path expressions would produce:
How to Extract a Field from a JSON Object in MySQL
Now that you understand how the JSON_EXTRACT MySQL function works and how to use MySQL's JSON path syntax, you are ready to see a real-world example.
Assume you have a table called config that stores each user's settings in a JSON column. Using a visual database MySQL client like DbVisualizer, you can easily explore the contents of this table:
As you can see, this sample table contains user settings stored in a MySQL JSON column named settings.
Now, let’s extract specific settings with JSON_EXTRACT in MySQL!
To get the theme setting of the user with id = 2, you can use the following query:
1
SELECT JSON_EXTRACT(settings, '$.theme') AS theme
2
FROM config
3
WHERE user_id = 2;
As you can see, the result will be "light":
Now, if you run the same query on a user whose JSON object doesn't contain a theme key, you will get NULL. For example:
1
SELECT JSON_EXTRACT(settings, '$.theme') AS theme
2
FROM config
3
WHERE user_id = 1;
See the NULL result:
Note that you can achieve the same result using the MySQL -> operator:
1
SELECT settings->'$.theme' AS theme
2
FROM config;
This time, you will get:
In particular, the -> operator is a shorthand for the MySQL JSON_EXTRACT function when used with two arguments.
Conclusion
In this guide, you learned what the JSON_EXTRACT MySQL function is, how it works, how to use it with advanced JSON path queries, and what kind of data it returns.
As demonstrated in a real-world example, DbVisualizer allows you to manage JSON data visually and intuitively. This is just one of its many powerful features—others include SQL formatting, ERD-style schema diagrams, query optimization tools, and more. Download DbVisualizer for free today!
FAQ
When was JSON support added to MySQL?
MySQL introduced basic support for the JSON data type in version 5.7.8, released in mid-2015. While this version allowed storing and retrieving JSON data, the functionality was limited. Significant improvements came with MySQL 8.0, released in April 2018. This version made JSON support far more practical and usable. So, while JSON was technically supported in MySQL 5.7, most of the features developers rely on today were added in version 8.0.
When was JSON_EXTRACT added to MySQL?
The JSON_EXTRACT MySQL function was introduced during the MySQL 5.7 development cycle. Although it existed in 5.7, it became much more useful and popular with the enhancements brought by MySQL 8.0. If you are still using MySQL 5.7 (which has now reached end-of-life and is no longer maintained), you can use JSON_EXTRACT, but you will be missing out on newer JSON-related features and performance improvements.
Do other databases support JSON?
Yes, several major relational databases support JSON, including:
What is the difference between the JSON_EXTRACT MySQL function and the ->/->> operators?
JSON_EXTRACT and the -> operator both return JSON values from a JSON column. The ->> operator, however, returns the unquoted scalar value (as plain text). In short:
Why use a visual database client to deal with JSON data in MySQL?
A visual database client like DbVisualizer makes it easier to view, query, and explore nested JSON structures, which can be difficult to interpret in raw SQL output. It improves readability, debugging, and understanding of complex data without writing extra formatting queries. These are only some of the advanced features that a powerful database client can offer. Grab a 21-day free trial for DbVisualizer Pro to discover them all!
