MySQL - 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

$sql = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)';
$mysqli->query($sql);

sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,SONGS JSON)";
con.query(sql)

String sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)";
statement.execute(sql);

create_table_query = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)'
cursorObj.execute(create_table_query)

Example

Following are the programs −

  $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();

ARRAY
"Kill this love"        

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    //Creating Blackpink table
    sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,SONGS JSON)";
    con.query(sql);

    sql = "INSERT INTO Blackpink (ID, SONGS) VALUES (ID, JSON_ARRAY('Pink venom','Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'));"
    con.query(sql);

    sql = "select * from blackpink;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Records in Blackpink Table");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SELECT JSON_TYPE(songs) FROM Blackpink;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Type of the column");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SELECT JSON_EXTRACT(songs, '$[2]') FROM Blackpink;"
    con.query(sql, function(err, result){
      console.log("fetching the third element in the songs array ");
      if (err) throw err
      console.log(result);
    });
});

 
Connected!
--------------------------
Records in Blackpink Table
[
  {
    ID: 1,
    SONGS: [
      'Pink venom',
      'Shutdown',
      'Kill this love',
      'Stay',
      'BOOMBAYAH',
      'Pretty Savage',
      'PLAYING WITH FIRE'
    ]
  }
]
--------------------------
Type of the column
[ { 'JSON_TYPE(songs)': 'ARRAY' } ]
--------------------------
fetching the third element in the songs array
[ { "JSON_EXTRACT(songs, '$[2]')": 'Kill this love' } ]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Json {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //create a table that takes a column of Json...!
         String sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         String sql1 = "INSERT INTO Blackpink (SONGS) VALUES (JSON_ARRAY('Pink venom', 'Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'))";
         statement.execute(sql1);
         System.out.println("Json data inserted successfully...!");

         // Now display the JSON type
         String sql2 = "SELECT JSON_TYPE(SONGS) FROM Blackpink";
         ResultSet resultSet = statement.executeQuery(sql2);
         while (resultSet.next()){
            System.out.println("Json_type:"+" "+resultSet.getNString(1));
         }

         // JSON_EXTRACT function to fetch the element
         String sql3 = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink";
         ResultSet resultSet1 = statement.executeQuery(sql3);
         while (resultSet1.next()){
            System.out.println("Song Name:"+" "+resultSet1.getNString(1));
         }

         connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}    

Connected successfully...!
Table created successfully...!
Json data inserted successfully...!
Json_type: ARRAY
Song Name: "Kill this love"      

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 'Blackpink' with JSON column
create_table_query = '''
CREATE TABLE Blackpink (
ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,
SONGS JSON
)'''
cursorObj.execute(create_table_query)
print("Table 'Blackpink' is created successfully!")
# Adding values into the above-created table
insert = """
INSERT INTO Blackpink (SONGS) VALUES
(JSON_ARRAY('Pink venom', 'Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'));
"""
cursorObj.execute(insert)
print("Values inserted successfully!")
# Display table
display_table = "SELECT * FROM Blackpink;"
cursorObj.execute(display_table)
# Printing the table 'Blackpink'
results = cursorObj.fetchall()
print("\nBlackpink Table:")
for result in results:
    print(result)
# Checking the type of the 'SONGS' column
type_query = "SELECT JSON_TYPE(SONGS) FROM Blackpink;"
cursorObj.execute(type_query)
song_type = cursorObj.fetchone()
print("\nType of the 'SONGS' column:")
print(song_type[0])
# Fetching the third element in the 'SONGS' array
fetch_query = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink;"
cursorObj.execute(fetch_query)
third_element = cursorObj.fetchone()
print("\nThird element in the 'SONGS' array:")
print(third_element[0])
# Closing the cursor and connection
cursorObj.close()
connection.close()     

Table 'Blackpink' is created successfully!
Values inserted successfully!

Blackpink Table:
(1, '["Pink venom", "Shutdown", "Kill this love", "Stay", "BOOMBAYAH", "Pretty Savage", "PLAYING WITH FIRE"]')

Type of the 'SONGS' column:
ARRAY

Third element in the 'SONGS' array:
"Kill this love"