Home » MySQL JSON » MySQL JSON_EXTRACT() Function

MySQL JSON_EXTRACT() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_EXTRACT() function to retrieve data from a JSON document.

Introduction to the MySQL JSON_EXTRACT() function

The JSON_EXTRACT() function allows you to extract data from a JSON document based on the specified JSON paths.

Here’s the syntax of the JSON_EXTRACT() function:

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

In this syntax:

  • json_doc: This is the JSON document from which you want to extract data.
  • path: This is the path expression that specifies the location of the data in the JSON document you want to extract.

The JSON_EXTRACT() function returns the values that match the paths. If multiple paths match, it returns an array that wraps the matched values.

If the path expression doesn’t locate any element in the json_doc, the function returns NULL. If any argument argument is NULL, the function also returns NULL.

MySQL JSON_EXTRACT() function examples

Let’s take some examples of using the JSON_EXTRACT() function.

1) Extracting single values

The following example uses the JSON_EXTRACT() function to extract the value associated with the name key from the JSON document:

SELECT 
  JSON_EXTRACT(
    '{"name": "John", "age": 25}', 
    '$.name'
  ) name;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| name   |
+--------+
| "John" |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Extracting array elements

The following example uses the JSON_EXTRACT() function to extract an array element at index 1:

SELECT 
  JSON_EXTRACT('[10, 20, 30]', '$[1]') result;Code language: SQL (Structured Query Language) (sql)

The $[1] specifies the element at index 1 which is the second element. Therefore, the JSON_EXTRACT() function returns 20:

+--------------------------------------+
| JSON_EXTRACT('[10, 20, 30]', '$[1]') |
+--------------------------------------+
| 20                                   |
+--------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Extracting nested values

The following example uses the JSON_EXTRACT() function to extract nested values by specifying multiple levels of keys in the path:

SELECT 
  JSON_EXTRACT(
    '{"person": {"name": "Jane", "age": 25}}', 
    '$.person.name'
  ) name;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+
| name    |
+---------+
| "Jane"  |
+---------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Handling non-existent paths

The following example uses the JSON_EXTRACT() function to extract data from a path that does not locate any element in the JSON document:

SELECT 
  JSON_EXTRACT(
    '{"name": "John", "age": 28}', '$.address.city'
  ) result;Code language: SQL (Structured Query Language) (sql)

It returns NULL:

+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

5) Using the MySQL JSON_EXTRACT() function with table data

We’ll use the products table from the sample database for the demonstration:

MySQL JSON_EXTRACT() Function

First, create a new table called product_prices with two columns id and data:

CREATE TABLE product_prices(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);Code language: SQL (Structured Query Language) (sql)

Second, create JSON documents from the data in the products table and insert them into the product_prices table:

INSERT INTO product_prices(data)
SELECT 
  JSON_OBJECT(
    'productName', productName, 
    'buyPrice', buyPrice, 
    'msrp', msrp
   )
FROM products;Code language: SQL (Structured Query Language) (sql)

Third, select data from the product_prices table:

SELECT * FROM product_prices;Code language: SQL (Structured Query Language) (sql)

Partial Output:

+-----+--------------------------------------------------------------------------------------------------+
| id  | data                                                                                             |
+-----+--------------------------------------------------------------------------------------------------+
|   1 | {"msrp": 95.70, "buyPrice": 48.81, "productName": "1969 Harley Davidson Ultimate Chopper"}       |
|   2 | {"msrp": 214.30, "buyPrice": 98.58, "productName": "1952 Alpine Renault 1300"}                   |
|   3 | {"msrp": 118.94, "buyPrice": 68.99, "productName": "1996 Moto Guzzi 1100i"}                      |
...Code language: SQL (Structured Query Language) (sql)

Finally, extract the product name, buy price, and MSRP from the JSON documents in the data column of the product_prices table:

SELECT 
  JSON_EXTRACT(
    data, 
    '$.productName', 
    '$.buyPrice', 
    '$.msrp'
  ) data 
FROM 
  product_prices;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------------------------+
| data                                                          |
+---------------------------------------------------------------+
| ["1969 Harley Davidson Ultimate Chopper", 48.81, 95.70]       |
| ["1952 Alpine Renault 1300", 98.58, 214.30]                   |
| ["1996 Moto Guzzi 1100i", 68.99, 118.94]                      |
...Code language: SQL (Structured Query Language) (sql)

Since we use multiple paths, the function returns the matched values wrapped in an array.

The -> operator

The -> operator is a shorthand for the JSON_EXTRACT() function when used with two arguments: a column identifier on the left and a JSON path on the right:

column_name -> pathCode language: SQL (Structured Query Language) (sql)

For example, we can extract the product name from the data column of the product_prices table as follows:

SELECT 
  data -> '$.productName'
FROM 
  product_prices;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------------------------+
| data -> '$.productName'                       |
+-----------------------------------------------+
| "1969 Harley Davidson Ultimate Chopper"       |
| "1952 Alpine Renault 1300"                    |
| "1996 Moto Guzzi 1100i"                       |
...Code language: SQL (Structured Query Language) (sql)

To remove the quotes from the result, you use the JSON_UNQUOTE() function:

SELECT 
  JSON_UNQUOTE(data -> '$.productName') productName 
FROM 
  product_prices;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------+
| productName                                 |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper       |
| 1952 Alpine Renault 1300                    |
| 1996 Moto Guzzi 1100i                       |
...Code language: SQL (Structured Query Language) (sql)

The ->> operator

The ->> operator is equivalent to either one of the following function calls:

JSON_UNQUOTE(column->path)
JSON_UNQUOTE(JSON_EXTRACT(column, path))Code language: SQL (Structured Query Language) (sql)

For example:

SELECT 
  data ->> '$.productName' productName 
FROM 
  product_prices;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------+
| productName                                 |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper       |
| 1952 Alpine Renault 1300                    |
| 1996 Moto Guzzi 1100i                       |
...Code language: SQL (Structured Query Language) (sql)

Summary

  • Use the JSON_EXTRACT() function to extract values from a JSON document and return the values that match the specified paths.
  • Use column -> path operator whenever you use the JSON_EXTRACT() function with two arguments.
  • Use column ->> path operator if you want to extract and unquote the extracted values.
Was this tutorial helpful?
MySQL MEMBER OF
Previously
MySQL MEMBER OF
Up Next
MySQL JSON_VALUE() Function
MySQL JSON_VALUE() Function