In this tutorial, we will learn about MySQL JSON data type and how to use it inside the SQL queries to perform operations using simple and effective examples.
Introduction to MySQL JSON Datatype
The JSON datatype was introduced in MySQL version 5.7.8 which allows you to store and access the data in JSON documents.
JSON documents are stored in MySQL in an internal format that provides for easy access to document components. The JSON binary format is organized in such a manner that the server may quickly search for values within the JSON document by key or array index.
The maximum size of the JSON document is approximately similar to the LONGBLOB or LONGTEXT.
In this tutorial, we will see the syntax of defining the JSON datatype, and then we will see how to perform operations on it such as fetch, update and delete.
MySQL JSON Syntax
There is no additional statement required to define a JSON-type column. The following syntax is used to define a JSON type column-
CREATE TABLE table_name (
…
column_name JSON,
…
);Code language: SQL (Structured Query Language) (sql)Note that, the JSON type column can’t have any default value.
Let’s now start with creating a table and inserting values into it.
Creating a Table
Here, we will create a table to store object names and their properties. The properties will be stored in the JSON format because we have to key-value pairs to define properties.
CREATE TABLE animalsInfo(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
properties JSON
);Code language: SQL (Structured Query Language) (sql)That’s it. We have created our table having a JSON data type.
Insert Values into the Table
To insert the value into the JSON type column, you must use the following format-
INSERT INTO table(jsonCol) VALUES('{"key":"value"}')Code language: SQL (Structured Query Language) (sql)Note that, the single quotation marks outside the curly brackets are most important. Also, keys and values both must be inside the double quotation marks.
Now let’s insert some values into the table.
INSERT INTO animalsInfo(name,properties)VALUES
("Dog", '{"Scientific name":"Canis lupus familiaris","Lifespan":"13 Years","Type":"Omnivorous"}'),
("Cat", '{"Scientific name":"Felis catus","Lifespan":"14 Years","Type":"Obligate carnivores"}'),
("Rabbit",'{"Scientific name":"Oryctolagus cuniculus","Lifespan":"9 Years","Type":"Herbivores"}'),
("Elephant",'{"Scientific name":"Elephantidae","Lifespan":"48 Years","Type":"Herbivores"}');Code language: SQL (Structured Query Language) (sql)
Fetch Values from Table
To fetch the values from the JSON type column, you can use the path operator (->) along with the $ sign. Let’s see it through an example.
Syntax to fetch values from the JSON type column-
SELECT json_col->'$.property_name';Code language: SQL (Structured Query Language) (sql)Here, we will fetch the animal name and their age. Notice that, ‘age’ is one of the properties of the animal that is present in the ‘properties’ column.
SELECT name,properties->'$.Lifespan' AS Age FROM animalsInfo;Code language: SQL (Structured Query Language) (sql)Here, the ‘properties’ is the column name whereas the ‘Lifespan’ is one of the properties of the animal.
The ‘$’ sign along with the ‘.’ operator is important and both the part of the syntax.
As you can see, we have received the expected output.
Update the JSON Values
You can update the data in JSON fields using the JSON_REPLACE, JSON_SET and JSON_INSERT functions. The syntax to update the JSON values is-
UPDATE table SET json_col=
JSON_REPLACE(json_doc, path,val] ...)
WHERE condition;Code language: SQL (Structured Query Language) (sql)The syntax to use the JSON_SET and JSON_INSERT is the same as JSON_REPLACE.
Let’s update the value of the age of the animal dog. We will update it from ’13 years’ to ’14 years’.
UPDATE animalsInfo SET properties=JSON_REPLACE(
`properties`,'$.Lifespan','14 Years')
WHERE name="Dog";Code language: SQL (Structured Query Language) (sql)Here, we have provided three parameters to the JSON_REPLACE- The JSON document, the path and the new value.
Let’s display the table to check if the values are changed.
As you can see, the age of ‘dog’ has been changed from 13 years to 14 years.
Delete JSON Values
To delete the JSON data, you can use the JSON_REMOVE function.
Here, we will remove one of the properties of the cat using the JSON_REMOVE function.
Note that, the JSON_REMOVE function is used with the UPDATE statement and not with the DELETE statement.
UPDATE animalsInfo
SET properties=JSON_REMOVE(`properties`,"$.Type")
WHERE name='Cat';Code language: SQL (Structured Query Language) (sql)Here, we are trying to remove the ‘Type’ property from the properties of the cat.
As you can see, the ‘Type’ property has been deleted from the properties column.
Summary
In this tutorial, we have learned-
- What is a JSON Datatype
- Syntax of the JSON datatype
- To Create a table and insert the JSON values into it
- To Fetch JSON values from the table
- To update JSON values in the table
- To delete JSOn values from the table