Working with JSON data in SQL can be a game-changer, especially when dealing with semi-structured information stored in relational databases. One of the most useful functions for JSON manipulation in SQL is JSON_EXTRACT. In this article, I will explain how JSON_EXTRACT works, provide practical examples, and explore the best ways to use it efficiently.
Understanding JSON_EXTRACT in SQL
JSON_EXTRACT is a built-in function in MySQL, MariaDB, and some other SQL databases that allows us to extract values from a JSON document. It takes two arguments:
- The JSON column or data.
- The JSON path to the desired element.
Here’s the basic syntax:
JSON_EXTRACT(json_doc, path)Where:
json_doc: The JSON data stored in a column or provided as a string.path: The location of the value within the JSON document, using JSONPath notation.
Basic Example of JSON_EXTRACT
Let’s assume we have the following table named users:
| id | user_data |
|---|---|
| 1 | {“name”: “John”, “age”: 30, “city”: “New York”} |
| 2 | {“name”: “Alice”, “age”: 25, “city”: “Los Angeles”} |
To extract the name of each user, we can use:
SELECT JSON_EXTRACT(user_data, '$.name') AS name FROM users;The result:
| name |
|---|
| “John” |
| “Alice” |
Notice that the extracted values are enclosed in double quotes. To return them as plain text, use JSON_UNQUOTE:
SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.name')) AS name FROM users;Extracting Nested JSON Data
JSON_EXTRACT is particularly useful for dealing with complex JSON structures. Suppose we extend our user_data like this:
{
"name": "John",
"age": 30,
"address": {
"street": "5th Avenue",
"city": "New York"
}
}To extract the street name, we use:
SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.address.street')) AS street FROM users;Extracting JSON Arrays
What if we store multiple phone numbers for each user inside an array?
{
"name": "John",
"phones": ["+123456789", "+987654321"]
}To extract the first phone number:
SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.phones[0]')) AS first_phone FROM users;Or, to extract all phone numbers:
SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.phones')) AS phones FROM users;Filtering Data Based on JSON Values
One powerful use case of JSON_EXTRACT is filtering rows where a certain JSON property has a specific value. For example, to select all users from “New York”:
SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.city')) = 'New York';Performance Considerations
Using JSON_EXTRACT extensively in queries can lead to performance issues, especially with large datasets. Here are some best practices:
- Use indexed virtual columns: You can create a virtual column based on JSON data and index it for faster lookups.
- Avoid unnecessary JSON extractions: If you frequently need a specific field, consider storing it in a separate column instead of always extracting it from JSON.
- Use JSON-specific indexes (if available): Some databases, like MySQL 8.0, allow you to create JSON indexes for better performance.
Conclusion
JSON_EXTRACT is a powerful function for querying and handling JSON data in SQL. Whether you’re dealing with nested objects, filtering data, or extracting array elements, understanding how to use this function effectively can greatly improve your database handling capabilities.
Other interesting article:
How CASE_INSENSITIVE_COMPARE works in SQL? Best CASE_INSENSITIVE_COMPARE examples