MYSQL JSON OBJECT

MYSQL JSON Object#

MYSQL JSON Object is a feature in MYSQL that allows you to store JSON data in a column. This feature was introduced in MYSQL version 5.7.8. Before this version, you could not store JSON data in MYSQL.

JSON stands for JavaScript Object Notation. It is a lightweight data interchange format that is easy for humans to read and write and easy for machines to parse and generate.

How to create a table with a JSON column in MYSQL#

CREATE TABLE my_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    data JSON
);

In the above code, we create a table called "my_table" with three columns: id, name, and data. The id column is an auto-incrementing primary key, the name column is a required string, and the data column is a JSON object.

How to insert JSON data into a MYSQL JSON column#

INSERT INTO my_table (name, data) VALUES ('John', '{"age": 30, "city": "New York"}');

In the above code, we insert a row into the "my_table" table with the name "John" and a JSON object in the data column containing an age of 30 and a city of "New York".

How to query MYSQL JSON data#

You can use the MYSQL JSON functions to query JSON data in MYSQL.

• JSON_EXTRACT: extracts a value from a JSON object using a path expression
• JSON_SEARCH: searches a JSON object for a specified value and returns the path to the value
• JSON_CONTAINS: checks if a JSON object contains a specified value

Here is an example of using JSON_EXTRACT:

SELECT JSON_EXTRACT(data, '$.age') AS age FROM my_table WHERE name = 'John';

In the above code, we select the age value from the data column in the "my_table" table where the name is "John". The result will be a single row with the age value of 30.

Another way to query MYSQL JSON data is by using the -> operator:

SELECT data->'$.age' AS age FROM my_table WHERE name = 'John';

This will produce the same result as the previous query.

Conclusion#

The MYSQL JSON object feature is a powerful tool for storing and querying JSON data in MYSQL. It allows you to store structured data in a flexible format and query it using MYSQL's powerful SQL language.