Extracting and Unquoting JSON Data in MySQL

Query and Unquote JSON Data using JSON_UNQUOTE

Write a MySQL query to extract and unquote the "brand" value from the JSON document in the Details column using JSON_UNQUOTE.

Solution:

-- Extract and unquote the "brand" value from the JSON Details column.

-- Select the ProductName column and extract the "brand" value from the JSON Details column.
SELECT 
    -- Retrieve the ProductName column.
    ProductName, 
    
    -- Use JSON_EXTRACT to get the "brand" value from the JSON object in the Details column.
    -- JSON_UNQUOTE removes the surrounding quotes from the extracted JSON value.
    JSON_UNQUOTE(JSON_EXTRACT(Details, '$.brand')) AS Brand  -- Alias the result as Brand.

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

Explanation:

• Purpose of the Query:
• To retrieve a plain text value from a JSON document by removing enclosing quotes.
• Demonstrates the combination of JSON_EXTRACT and JSON_UNQUOTE for cleaner output.
• Key Components:
• JSON_EXTRACT(Details, '$.brand') : Retrieves the "brand" value from the JSON document.
• JSON_UNQUOTE(...) : Converts the JSON string to a plain text string.
• Real-World Application:
• Useful when the extracted JSON value is needed in a format that’s easily readable or comparable.

Notes:

• This method is particularly beneficial for reporting or display purposes.
• Ensure that the JSON path accurately targets the intended key.

For more Practice: Solve these Related Problems:

• Write a MySQL query to extract and unquote a nested JSON value from a complex JSON document.
• Write a MySQL query to unquote a JSON value and then compare it to a string literal in a WHERE clause.
• Write a MySQL query to extract, unquote, and combine two JSON values from different keys into a single output column.
• Write a MySQL query to unquote multiple JSON keys from a document and display them in separate columns.

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

Previous MySQL Exercise: Convert Relational Data to JSON Array of Objects Using JSON_ARRAYAGG.
Next MySQL Exercise: Retrieve Only Rows with Valid JSON Data Using JSON_VALID.