Today’s post guides you on how to extract the data from JSON type in MySQL using different ways. We will cover the general working of extracting the data from JSON type in MySQL and further see how to work with JSON data types in a MySQL table to extract the different data from columns.
How to Extract the JSON Data in MySQL
When you want to extract the JSON data in MySQL, the main function that you should use is JSON_EXTRACT which works with the following syntax:
You must specify two arguments when executing the function. The first argument is the JSON document. The other is the path to the value in the JSON data type that you want to extract. Let’s give various examples to help understand how to extract the data from JSON type in MySQL.
Example 1: Extracting One Value
The first example is a simple query that returns one value based on the specified path in the specified column in the JSON data. The following example specifies the path after the dot operator, and the path is a key name in the JSON data.
The output contains the specified key name that you aim to achieve and is printed out in quotes. To remove the quotes, you can use the JSON_VALUE() instead of the JSON_EXTRACT() as in the following example:
Example 2: Extracting Multiple Values
When extracting multiple paths in the JSON data, you must specify the target paths after a dot operator and separate them in commas. Let’s have an example where we want to extract two values in the same JSON data that we used in the previous example. For that, we have our example as shown in the following:
Example 3: Extracting the JSON Data from an Array
When you have your JSON data in an array, you can extract it by specifying the location of the particular value.
Here’s an example where we have an array of numbers and are extracting the value at position 3 using the “$” sign:
Example 4: Extracting the JSON Data from a Table
Suppose you have a table containing a column that has JSON. It’s possible to also extract the data from it. Let’s create a sample table that we will use for demonstration. We name our table as “course” which has three columns.
We can also insert the data into our table.
Our final table which contains the JSON data appears as follows:
Suppose we want to extract the JSON data in the “course_details” column in the table where the code is equals to F12. We execute the following command. Note that in the JSON_EXTRACT(), we specify the first argument as the column that contains the JSON data, and the second argument is the particular value in the JSON data.
When specifying the key name, you must start with the “$” and the dot operator. In our case, the key name is the “Code” and we want to find the values where the code matches a given data.
The previous output matched our expected outcome which confirms that we managed to extract the JSON data.
You can also specify the particular columns in your table that you want to retrieve. Then, you can use the “->” operator for the JSON data. It also stands for JSON_EXTRACT() and you identify the target value using the “$” and dot operators.
The following example selects two columns and extracts the “Name” key name from the JSON data for all entries:
You can eliminate the quotes in the extracted data by replacing the “->” operator with “->>” operator and then specify the target key name as in the following example:
Conclusion
This post explained how to extract the data from the JSON type in MySQL. We’ve given different examples of implementing the “extract” function and the various options that you can use. Hopefully, that fixed your query and helped you understand how to work with the JSON data in MySQL.
