Extracting JSON Object Keys in MySQL

Retrieve JSON Object Keys Using JSON_KEYS

Write a MySQL query to extract all keys from a JSON document stored in the Details column of the Products table using the JSON_KEYS function.

Solution:

-- Retrieve all keys from the JSON document in the Details column.

-- Select the ProductName column and extract all keys from the JSON object in the Details column.
SELECT 
    -- Retrieve the ProductName column.
    ProductName, 
    
    -- Use JSON_KEYS to extract all top-level keys from the JSON object in the Details column.
    -- The result is returned as a JSON array of keys.
    JSON_KEYS(Details) AS JSONKeys  -- Alias the resulting JSON array of keys as JSONKeys.

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

Explanation:

• Purpose of the Query:
• To extract the list of keys from a JSON document stored in a table.
• Demonstrates how to use the JSON_KEYS function to inspect the structure of JSON data.
• Key Components:
• JSON_KEYS(Details) : Retrieves an array of keys from the JSON document.
• AS JSONKeys : Provides a clear alias for the resulting column.
• Real-World Application:
• Useful for dynamically understanding the schema of semi-structured data in applications that store JSON.

Notes:

• Ensure that the Details column contains valid JSON data.
• The output is a JSON array listing all keys present at the top level of the JSON document.

For more Practice: Solve these Related Problems:

• Write a MySQL query to extract only the top-level keys from a JSON column, ignoring any nested keys.
• Write a MySQL query to retrieve JSON object keys from rows where the JSON document contains an array element.
• Write a MySQL query to extract JSON keys and sort them alphabetically within the result.
• Write a MySQL query to filter out numeric keys when extracting keys from a JSON document.

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

Previous MySQL Exercise: Update Multiple Values in a JSON Column Using JSON_SET.
Next MySQL Exercise: Search for a Specific Value in JSON Using JSON_CONTAINS.