Extracting Nested JSON Values in MySQL

Extract Nested JSON Value

Write a MySQL query to extract a nested value from a JSON document stored in a column.

Solution:

-- Retrieve the storage capacity from the nested specs in the Details JSON.

-- Select the ProductName column and extract the 'storage' field from the nested 'specs' object in the JSON data.
SELECT 
    -- Retrieve the ProductName column.
    ProductName, 
    
    -- Use JSON_EXTRACT to access the 'storage' field within the nested 'specs' object.
    -- The path '$.specs.storage' specifies the location of the 'storage' field in the JSON object.
    JSON_EXTRACT(Details, '$.specs.storage') AS Storage

-- Specify the table from which to retrieve the data.
FROM Products; 

Explanation:

• Purpose of the Query:
• To extract the nested "storage" value from the "specs" object within the JSON document.
• Demonstrates handling of multi-level JSON structures.
• Key Components:
• JSON_EXTRACT(Details, '$.specs.storage') : Navigates to the nested "storage" key.
• AS Storage : Renames the output column for clarity.
• Real-World Application:
• Useful for retrieving specific details from complex JSON structures stored in the database.

Notes:

• Ensure the JSON document follows the expected structure for accurate extraction.
• This method can be extended to any depth within the JSON document.

For more Practice: Solve these Related Problems:

• Write a MySQL query to extract a value nested two levels deep inside a JSON object.
• Write a MySQL query to extract a nested JSON value and assign it a custom alias in the result.
• Write a MySQL query to extract a nested value using an alternative JSON path syntax (e.g., arrow operator).
• Write a MySQL query to extract a nested JSON key value and provide a default fallback if the key does not exist.

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

Previous MySQL Exercise: Validate JSON Data using JSON_VALID.
Next MySQL Exercise: Merge two JSON Documents Using JSON_MERGE_PRESERVE.