Course
JSON
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
JSON
MySQL provides a native JSON (JavaScript Object Notation) datatype that enables efficient access to the data in JSON documents. This datatype is introduced in MySQL versions 5.7.8 and later.
Before it was introduced, the JSON-format strings were stored in the string columns of a table. However, the JSON datatype proves to be more advantageous than strings due to the following reasons
- It automatically validates the JSON documents, displaying an error whenever an invalid document is stored.
- It stores the JSON documents in an internal format allowing easy read access to the document elements. Hence, when the MySQL server later reads the stored JSON values in a binary format, it just enables the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.
The storage requirements for JSON documents are similar to those of LONGBLOB or LONGTEXT data types.
MySQL JSON
To define a table column with JSON datatype, we use the keyword JSON in the CREATE TABLE statement.
We can create two types of JSON values in MySQL:
- JSON array: It is a list of values separated by commas and enclosed within square brackets ([]).
- JSON object: An object with a set of key-value pairs separated by commas and enclosed within curly brackets ({}).
Syntax
Following is the syntax to define a column whose data type is JSON
CREATE TABLE table_name ( ... column_name JSON, ... );Example
Let us see an example demonstrating the usage of JSON datatype in a MySQL table. Here, we are creating a table named MOBILES using the following query
CREATE TABLE MOBILES( ID INT NOT NULL, NAME VARCHAR(25) NOT NULL, PRICE DECIMAL(18,2), FEATURES JSON, PRIMARY KEY(ID));Now, let us insert values into this table using the INSERT statement. In the FEATURES column, we use key-value pairs as a JSON value.
INSERT INTO MOBILES VALUES(121, 'iPhone 15', 90000.00, '{"OS": "iOS", "Storage": "128GB", "Display": "15.54cm"}'),(122, 'Samsung S23', 79000.00, '{"OS": "Android", "Storage": "128GB", "Display": "15.49cm"}'),(123, 'Google Pixel 7', 59000.00, '{"OS": "Android", "Storage": "128GB", "Display": "16cm"}');Output
The table will be created as
Retrieving Data From JSON Column
As JSON datatype provides an easier read access to all JSON elements, we can also retrieve each element directly from the JSON column. MySQL provides a JSON_EXTRACT() function to do so.
Syntax
Following is the syntax of the JSON_EXTRACT() function
JSON_EXTRACT(json_doc, path)In a JSON array, we can retrieve a particular element by specifying its index (starting from 0). And in a JSON object, we specify the key from key-value pairs.
Example
In this example, from the previously created MOBILES table we are retrieving the OS name of each mobile using the following query
SELECT NAME, JSON_EXTRACT(FEATURES,'$.OS') AS OS FROM MOBILES;Instead of calling the function, we can also use -> as a shortcut for JSON_EXTRACT. Look at the query below −
SELECT NAME, FEATURES->'$.OS' AS OS FROM MOBILES;Output
Both queries display the same following output
The JSON_UNQUOTE() Function
The JSON_UNQUOTE() function is used to remove the quotes while retrieving the JSON string. Following is the syntax
JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))Example
In this example, let us display the OS name of each mobile without the quotes
SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,'$.OS')) AS OS FROM MOBILES;Or, we can use ->> as a shortcut for JSON_UNQUOTE(JSON_EXTRACT(...)).
SELECT NAME, FEATURES->>'$.OS' AS OS FROM MOBILES;Output
Both queries display the same following output
We cannot use chained -> or ->> to extract data from nested JSON object or JSON array. These two can only be used for the top level.
The JSON_TYPE() Function
As we know, the JSON field can hold values in the form of arrays and objects. To identify which type of values are stored in the field, we use the JSON_TYPE() function. Following is the syntax
JSON_TYPE(json_doc)Example
In this example, let us check the type of the FEATURES column of MOBILES table using JSON_TYPE() function.
SELECT JSON_TYPE(FEATURES) FROM MOBILES;Output
As we can see in the output, the songs column type is OBJECT.
The JSON_ARRAY_APPEND() Function
If we want to add another element to the JSON field in MySQL, we can use the JSON_ARRAY_APPEND() function. However, the new element will only be appended as an array. Following is the syntax
JSON_ARRAY_APPEND(json_doc, path, new_value);Example
Let us see an example where we are adding a new element at the end of the JSON object using the JSON_ARRAY_APPEND() function
UPDATE MOBILES SET FEATURES = JSON_ARRAY_APPEND(FEATURES,'$',"Resolution:2400x1080 Pixels");We can verify whether the value is added or not using a SELECT query
SELECT NAME, FEATURES FROM MOBILES;Output
The table will be updated as
The JSON_ARRAY_INSERT() Function
We can only insert a JSON value at the end of the array using the JSON_ARRAY_APPEND() function. But, we can also choose a position to insert a new value into the JSON field using the JSON_ARRAY_INSERT() function. Following is the syntax
JSON_ARRAY_INSERT(json_doc, pos, new_value);Example
Here, we are adding a new element in the index=1 of the array using the JSON_ARRAY_INSERT() function
UPDATE MOBILES SET FEATURES = JSON_ARRAY_INSERT( FEATURES, '$[1]', "Charging: USB-C");To verify whether the value is added or not, display the updated table using the SELECT query
SELECT NAME, FEATURES FROM MOBILES;Output
The table will be updated as
JSON Using Client Program
We can also define a MySQL table column with the JSON datatype using Client Program.
Syntax
PHPNodeJSJavaPython
To create a column of JSON type through a PHP program, we need to execute the CREATE TABLE statement with JSON datatype on a column using the mysqli function query() as follows
$sql = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)';$mysqli->query($sql);Example
Following are the programs
PHPNodeJSJavaPython
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit(); } // Create table Blackpink $sql = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)'; $result = $mysqli->query($sql); if ($result) { echo "Table created successfully...!"; } // Insert data into the created table $q = "INSERT INTO Blackpink (SONGS) VALUES (JSON_ARRAY('Pink venom', 'Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'))"; if ($res = $mysqli->query($q)) { echo "Data inserted successfully...!"; } // Now display the JSON type $s = "SELECT JSON_TYPE(SONGS) FROM Blackpink"; if ($res = $mysqli->query($s)) { while ($row = mysqli_fetch_array($res)) { echo $row[0] . "\n"; } } else { echo 'Failed'; } // JSON_EXTRACT function to fetch the element $sql = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink"; if ($r = $mysqli->query($sql)) { while ($row = mysqli_fetch_array($r)) { echo $row[0] . "\n"; } } else { echo 'Failed'; } $mysqli->close();Output
The output obtained is as shown below
ARRAY"Kill this love"