Extracting Values from JSON Documents in MySQL

Query JSON Data with JSON_EXTRACT

Write a SQL query to extract a specific value from a JSON document stored in a column using the JSON_EXTRACT function

Solution:

-- Retrieve the brand from the JSON details of a product.
SELECT ProductName, JSON_EXTRACT(Details, '$.brand') AS Brand
FROM Products;

Explanation:

• Purpose of the Query:
• To extract the "brand" value from the JSON stored in the Details column.
• Demonstrates how to navigate and retrieve data from a JSON document.
• Key Components:
• JSON_EXTRACT(Details, '$.brand') : Extracts the value associated with the key "brand".
• AS Brand : Renames the output column for clarity.
• Real-World Application:
• Enables applications to parse and display specific product attributes stored as JSON.

Notes:

• The JSON path '$.brand' directly references the key at the root level of the JSON document.
• This function is essential when working with semi-structured data in MySQL.

For more Practice: Solve these Related Problems:

• Write a MySQL query to extract a specific element from a JSON array stored in a column.
• Write a MySQL query to retrieve a value nested three levels deep in a JSON document using JSON_EXTRACT.
• Write a MySQL query to extract multiple keys from a JSON object in a single query.
• Write a MySQL query to extract a JSON value using a conditional JSON path that handles missing keys gracefully.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Insert JSON Data into a Column.
Next MySQL Exercise: Update a JSON Document with JSON_SET.