Creating Generated Columns from JSON Data

Create a Generated Column from JSON Data

Write a MySQL query to add a generated column that extracts a specific value from a JSON column and index it for faster querying.

Solution:

-- Add a generated column to extract the brand from the JSON Details and index it.

-- Modify the Products table to add a new column and an index.
ALTER TABLE Products 

-- Add a new column named 'Brand' of type VARCHAR(50).
ADD COLUMN Brand VARCHAR(50) 
  -- Define the column as a generated column that extracts the 'brand' field from the JSON Details.
  -- JSON_EXTRACT retrieves the 'brand' field, and JSON_UNQUOTE removes the surrounding quotes.
  GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(Details, '$.brand'))) STORED,

-- Add an index named 'idx_brand' on the newly created Brand column for faster queries.
ADD INDEX idx_brand (Brand);  

Explanation:

• Purpose of the Query:
• To create a virtual column that extracts the "brand" from JSON data for optimized search operations.
• Demonstrates how generated columns can improve performance by indexing extracted JSON values.
• Key Components:
• GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(Details, '$.brand'))) : Extracts and unquotes the brand value.
• ADD INDEX idx_brand (Brand) : Indexes the generated column for faster lookups.
• Real-World Application:
• Essential for applications that frequently query JSON attributes, enabling efficient filtering.

Notes:

• Generated columns must be stored to be indexed.
• Ensure the JSON structure is consistent to avoid errors during extraction.

For more Practice: Solve these Related Problems:

• Write a MySQL query to add a generated column that extracts a nested key from a JSON column.
• Write a MySQL query to create a generated column that extracts an element from a JSON array and then index that column.
• Write a MySQL query to add a generated column that extracts and unquotes a JSON key value for faster querying.
• Write a MySQL query to create multiple generated columns from a JSON document, each extracting a different key.

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

Previous MySQL Exercise: Merge two JSON Documents Using JSON_MERGE_PRESERVE.
Next MySQL Exercise: Convert Row Data to JSON Using JSON_OBJECT.