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