MySQL - JSON

MySQL 提供了原生的 JSON（JavaScript 物件表示法）資料型別，它可以有效地訪問 JSON 文件中的資料。此資料型別在 MySQL 5.7.8 及更高版本中引入。

在引入它之前，JSON 格式的字串儲存在表的字串列中。但是，由於以下原因，JSON 資料型別被證明比字串更有優勢：

• 它會自動驗證 JSON 文件，並在儲存無效文件時顯示錯誤。
• 它以內部格式儲存 JSON 文件，從而可以輕鬆讀取文件元素。因此，當 MySQL 伺服器稍後以二進位制格式讀取儲存的 JSON 值時，它只是使伺服器能夠透過鍵或陣列索引直接查詢子物件或巢狀值，而無需讀取文件中之前或之後的全部值。

JSON 文件的儲存需求類似於LONGBLOB或LONGTEXT資料型別。

MySQL JSON

要使用 JSON 資料型別定義表列，我們在 CREATE TABLE 語句中使用關鍵字JSON。

我們可以在 MySQL 中建立兩種型別的 JSON 值

• JSON 陣列：它是用方括號([])括起來的、用逗號分隔的值列表。

• JSON 物件：一個物件，包含用花括號({})括起來的、用逗號分隔的一組鍵值對。

語法

以下是定義資料型別為 JSON 的列的語法：

CREATE TABLE table_name (
   ...
   column_name JSON,
   ... 
);

示例

讓我們看一個演示如何在 MySQL 表中使用 JSON 資料型別的示例。在這裡，我們使用以下查詢建立一個名為MOBILES的表：

CREATE TABLE MOBILES(
   ID INT NOT NULL,
   NAME VARCHAR(25) NOT NULL,
   PRICE DECIMAL(18,2),
   FEATURES JSON,
   PRIMARY KEY(ID)
);

現在，讓我們使用 INSERT 語句將值插入此表。在 FEATURES 列中，我們使用鍵值對作為 JSON 值。

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"}');

輸出

表將建立為：

從 JSON 列檢索資料

由於 JSON 資料型別提供對所有 JSON 元素的更輕鬆的讀取訪問，因此我們還可以直接從 JSON 列中檢索每個元素。MySQL 提供了一個 JSON_EXTRACT() 函式來執行此操作。

語法

以下是 JSON_EXTRACT() 函式的語法：

JSON_EXTRACT(json_doc, path)

在 JSON 陣列中，我們可以透過指定其索引（從 0 開始）來檢索特定元素。在 JSON 物件中，我們指定鍵值對中的鍵。

示例

在此示例中，從之前建立的 MOBILES 表中，我們使用以下查詢檢索每部手機的作業系統名稱：

SELECT NAME, JSON_EXTRACT(FEATURES,'$.OS') 
AS OS FROM MOBILES;

我們也可以使用->作為 JSON_EXTRACT 的快捷方式，而不是呼叫函式。請檢視以下查詢：

SELECT NAME, FEATURES->'$.OS' 
AS OS FROM MOBILES;

輸出

這兩個查詢都顯示以下相同的輸出：

JSON_UNQUOTE() 函式

JSON_UNQUOTE() 函式用於在檢索 JSON 字串時刪除引號。以下是語法：

JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))

示例

在此示例中，讓我們顯示每部手機的作業系統名稱，不帶引號：

SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,'$.OS')) 
AS OS FROM MOBILES;

或者，我們可以使用->>作為JSON_UNQUOTE(JSON_EXTRACT(...))的快捷方式。

SELECT NAME, FEATURES->>'$.OS' 
AS OS FROM MOBILES;

輸出

這兩個查詢都顯示以下相同的輸出：

我們不能使用鏈式 -> 或 ->> 從巢狀的 JSON 物件或 JSON 陣列中提取資料。這兩個運算子只能用於頂層。

JSON_TYPE() 函式

眾所周知，JSON 欄位可以儲存陣列和物件形式的值。為了識別字段中儲存的值型別，我們使用 JSON_TYPE() 函式。以下是語法：

JSON_TYPE(json_doc)

示例

在這個例子中，讓我們使用JSON_TYPE()函式檢查 MOBILES 表的 FEATURES 列的型別。

SELECT JSON_TYPE(FEATURES) FROM MOBILES;

輸出

從輸出中可以看到，songs 列的型別是 OBJECT。

JSON_ARRAY_APPEND() 函式

如果想在 MySQL 中向 JSON 欄位新增另一個元素，可以使用 JSON_ARRAY_APPEND() 函式。但是，新元素只會作為陣列追加。以下是語法：

JSON_ARRAY_APPEND(json_doc, path, new_value);

示例

讓我們看一個例子，我們使用JSON_ARRAY_APPEND()函式在 JSON 物件的末尾新增一個新元素：

UPDATE MOBILES 
SET FEATURES = JSON_ARRAY_APPEND(FEATURES,'$',"Resolution:2400x1080 Pixels");

我們可以使用 SELECT 查詢驗證值是否已新增：

SELECT NAME, FEATURES FROM MOBILES;

輸出

表將更新為：

JSON_ARRAY_INSERT() 函式

我們只能使用 JSON_ARRAY_APPEND() 函式在陣列末尾插入 JSON 值。但是，我們也可以選擇一個位置，使用 JSON_ARRAY_INSERT() 函式將新值插入 JSON 欄位。以下是語法：

JSON_ARRAY_INSERT(json_doc, pos, new_value);

示例

這裡，我們使用JSON_ARRAY_INSERT()函式在陣列的 index=1 處新增一個新元素：

UPDATE MOBILES 
SET FEATURES = JSON_ARRAY_INSERT(
   FEATURES, '$[1]', "Charging: USB-C"
);

為了驗證值是否已新增，請使用 SELECT 查詢顯示更新後的表：

SELECT NAME, FEATURES FROM MOBILES;

輸出

表將更新為：

使用客戶端程式的 JSON

我們還可以使用客戶端程式定義一個具有 JSON 資料型別的 MySQL 表列。

語法

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

示例

以下是程式：

  $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://: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"