MySQL JSON_EXTRACT – How to Extract Data From JSON Document?

In this tutorial, we will learn how we can return specific values or data from the JSON document using the MySQL function JSON_EXTRACT(). It is super simple to understand and implement. We will see multiple examples and ways to extract the values from the JSON data in this tutorial. So without further ado, let’s get started!

Also read: Export MySQL Table to a CSV File?

Introduction to MySQL JSON_EXTRACT()

In the application, there might be chances that using the data in JSON format is the best way for insert, update and delete operations. For example, when you don’t know the data type of the input values, you can simply use the JSON format and store it in the database table.

For this, MySQL provides us with the very helpful function JSON_EXTRACT() to return the desired values from the JSON string or a JSON document.

MySQL provides a wide range of JSON functions to perform operations on the JSON data. The JSON_EXTRACT() is one of the most helpful features of MySQL that you would have thought might not available.

Anyway, let’s get started with the syntax of the JSON_EXTRACT().

Syntax of MySQL JSON_EXTRACT()

The JSON_EXTRACT() function requires two mandatory arguments. The first is the JSON document and the second is the path of the value to access from the specified JSON document.

JSON_EXTRACT(json_doc, path[, path] ...)Code language: SQL (Structured Query Language) (sql)

Here,

The json_doc is the JSON data or the JSON document. Whereas, the path is the path of the value to access.

Note that, there can be multiple paths you can specify in the function. If the function returns multiple values, the format of returning the result will be an array.

To access the values from the JSON document, we use the $ symbol with the given key name if the data contains key-value pairs with the dot operator in between. In the case of JSON array data, we simply use the $ sign with the square brackets and the index.

Examples of MySQL JSON_EXTRACT()

We will see examples of all the aspects down below.

Example 1 – Basic Usage of the JSON_EXTRACT()

Here we will write a simple query to return the desired value from the JSON data.

SELECT JSON_EXTRACT(
'{ "firstName":"Peter", "lastName":"Parker" }' , 
'$.firstName') AS firstName;Code language: SQL (Structured Query Language) (sql)

Note that, the JSON string must be enclosed by the single quotes and the key-value pairs must be in the double quotes. Otherwise, you will get an error saying that “invalid JSON text”.

The second argument is the path. Note that the key name must come after the dot operator. That’s how you access the value from the JSON data.

Now let’s execute the given query and see the result.

As you can see, we got the first name from the JSON data.

Example 2 – Multiple Paths in the JSON_EXTRACT()

Now let’s suppose you need multiple values from the JSON data. For this, you can specify multiple paths.
Note that, the returning result will be in the form of an array.

Let’s take an example.

SELECT JSON_EXTRACT(
' { "firstName":"Peter", 
"lastName":"Parker" ,
"age":21,
"city":"NewYork",
"school":"WNYPS" } ' , 
'$.firstName','$.lastName','$.age') AS basicInfo;Code language: SQL (Structured Query Language) (sql)

Here, we will get the first name, last name and age from the JSON data in the form of an array.

As you can see here, the output is wrapped inside the square brackets which means it is in the array format.

Example 3 – Extracting Values from an Array

The JSON data might contain the array string. Note that, the array can be of any data type. For example, an array of names (strings), an array of ages (numbers), an array of characters, etc.

The following method can extract the value from the array JSON data of any data type.

SELECT JSON_EXTRACT(
'[10,40,20,50,30,90,80]' , '$[2]')
AS getArrVal;Code language: SQL (Structured Query Language) (sql)

Here, we are returning the value from the array of the index position 2. Note that, the array index starts from 0. Therefore, the second index will return the third value from the array.

As you can see, we got the correct output.

Example 4 – Returning Nested Values

The JSON data might contain the key-value pair where the value can be an array or another object inside. In this case, we can also retrieve the value from the nested objects.

Let’s take an example below-

SET @data = '{  
    "Student": {    
       "Name": "Vinod", 
       "Age": 21,
       "Hobbies": {
        "basic":["reading","writing"],
        "tech":["coding","blogging"],
        "sports":["swimming","running"],
        "music":["piano","guitar"]
       }
    }
 }';

SELECT JSON_EXTRACT(@data, '$.Student.Hobbies.basic[0]')
AS hobbyZero;Code language: SQL (Structured Query Language) (sql)

Here, we have set a session variable and assigned a JSON string to it. The JSON string contains one object of the name “Student”. Inside it, we have another object called “Hobbies”. Inside the hobbies object, each value is of the type array.

It’s a little bit complex at first but it’s super simple. It’s just nested objects where values are extended down as a new object.

Then we used the SELECT statement to get the value from the hobbies object. Note that, we need the basic hobby of the index zero. Hence, we will combine example 1 and example 3 here.

Let’s execute the query.

As you can see, we got the expected output. But what if we want all our hobbies? We can simply write the key name here. Note that, if you just write the Hobbies in the path, you will the whole object body which includes all three hobby types.

SELECT JSON_EXTRACT(@data, '$.Student.Hobbies.basic') 
AS basicHobbies;Code language: SQL (Structured Query Language) (sql)

As you can see here, we got all the basic hobbies from the object.

Example 5: Non-Existing Path

When you try to retrieve the value from the JSON string which does not exist, you will get the NULL result.

Let’s try.

SELECT JSON_EXTRACT(
'[10,40,20,50]' , '$[6]')
AS getArrVal;Code language: SQL (Structured Query Language) (sql)

Here, we are trying to get the value from the sixth index position. However, there are only four values present in the JSON string. Therefore, we should get the result as NULL.

As you can see, we got the NULL value.

Summary

In this tutorial, we have learned about the JSON_EXTRACT function which is very helpful to work with JSON string data. We took a bunch of examples from different perspectives so that you can understand how the function behaves under different inputs. I hope you find this tutorial helpful. If you do, don’t forget to share it with your friends and let them learn the cool stuff too.

References

MySQL official documentation on the JSON_EXTRACT function.