MySQL JSON Extract: Keys & Values

1 year ago
Sophia Anderson
1 minute

To extract the keys and values from a JSON in MySQL, you can use the JSON_EXTRACT and JSON_KEYS functions.

For example, suppose there is a JSON field named data that contains the following data:

{
  "name": "John",
  "age": 30,
  "city": "New York"
}

To extract all keys and values, you can use the following SQL statement:

SELECT 
  JSON_KEYS(data) AS keys,
  JSON_EXTRACT(data, '$.name') AS name,
  JSON_EXTRACT(data, '$.age') AS age,
  JSON_EXTRACT(data, '$.city') AS city
FROM table_name;

This will return a result set containing keys and values. You can adjust the path in JSON_EXTRACT as needed to extract different keys and values.

bannerAds