MySQL JSON_KEYS

The JSON_KEYS function in MySQL is used to extract the keys from a JSON object. It returns a JSON array containing the keys of the input JSON object. This function can be particularly useful when you want to retrieve the keys of a JSON document and use them for further processing or analysis.

Syntax

Here is the basic syntax of the JSON_KEYS function:

JSON_KEYS(json_doc)

json_doc: The JSON document from which you want to extract the keys.

Example

Now, let’s look at an example to illustrate how to use the JSON_KEYS function:

Suppose you have a table named employee with a column named data that stores JSON documents representing employee information:

CREATE TABLE employee (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO employee VALUES
(1, '{"name": "John Doe", "age": 30, "department": "IT"}'),
(2, '{"name": "Jane Smith", "age": 25, "department": "HR"}'),
(3, '{"name": "Bob Johnson", "age": 35, "department": "Finance"}');

Now, you can use the JSON_KEYS function to retrieve the keys of the JSON objects stored in the data column:

SELECT 
id, JSON_KEYS(data) AS keys_json 
FROM employee;

The result of this query would be:

+----+-------------------------------+
| id | keys_json                     |
+----+-------------------------------+
| 1  | ["name", "age", "department"] |
| 2  | ["name", "age", "department"] |
| 3  | ["name", "age", "department"] |
+----+-------------------------------+

In this example, the JSON_KEYS function is used to extract the keys from the JSON objects stored in the data column. The result is a JSON array containing the keys for each row in the employee table.

You can further use this information for various purposes, such as dynamic querying or processing specific keys within your application.

The JSON_KEYS function is a powerful feature for working with JSON data in MySQL databases. It can be used to efficiently extract and manipulate property names from JSON objects, enabling you to perform various data analysis and manipulation tasks.