w3resource

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.

Go to:


PREV : Update Multiple Values in a JSON Column Using JSON_SET.
NEXT : Search for a Specific Value in JSON Using JSON_CONTAINS.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.