JSON_TABLE mysql适配版本

原创

mob64ca12e51ecb 2024-01-21 05:06:31 ©著作权

文章标签 JSON 数据字段 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12e51ecb的原创作品，请联系作者获取转载授权，否则将追究法律责任

JSON_TABLE MySQL适配版本

简介

在数据库中，我们经常需要将非结构化的数据转换为结构化的形式来进行处理和分析。JSON是一种常见的非结构化数据格式，而JSON_TABLE则是MySQL 8.0版本中引入的一个功能，用于将JSON数据转换为关系型数据并进行查询。

JSON_TABLE可以将JSON字段解析为一张虚拟表，从而可以使用SQL来对JSON数据进行查询和操作。它提供了一种方便的方式来处理和分析非结构化的JSON数据。

使用方法

在MySQL 8.0版本中，可以使用JSON_TABLE函数来创建JSON_TABLE表达式。JSON_TABLE函数的语法如下：

JSON_TABLE(json_doc, path COLUMNS (column_list) [AS] alias)

json_doc：要解析的JSON数据。
path：指定要解析的JSON字段的路径。
column_list：指定要从JSON字段中提取的列。
alias：可选，指定JSON_TABLE表达式的别名。

示例

下面我们将通过一个例子来演示JSON_TABLE的使用。假设我们有一个存储了学生信息的表students，其中的info字段保存了学生的个人信息，它是一个包含学生姓名、年龄和性别的JSON字符串。

首先，我们需要创建一个students表并插入一些数据，示例代码如下：

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  info JSON
);

INSERT INTO students (info) VALUES
  ('{"name": "Alice", "age": 18, "gender": "female"}'),
  ('{"name": "Bob", "age": 20, "gender": "male"}'),
  ('{"name": "Charlie", "age": 19, "gender": "male"}');

现在，我们可以使用JSON_TABLE来查询和操作这些学生信息。例如，我们可以使用JSON_TABLE来提取学生的姓名和年龄，示例代码如下：

SELECT name, age
FROM students, JSON_TABLE(info, '$'
  COLUMNS (
    name VARCHAR(255) PATH '$.name',
    age INT PATH '$.age'
  )
) AS t;

运行以上代码，我们将得到以下结果：

name	age
Alice	18
Bob	20
Charlie	19

JSON_TABLE的更多用法

除了基本的路径查询，JSON_TABLE还提供了其他一些用法来处理JSON数据。下面是一些常用的用法：

数组解析

如果JSON字段中包含一个数组，我们可以使用JSON_TABLE来将数组解析为多行数据。例如，我们有一个存储了学生的成绩信息的表students，其中的grades字段保存了学生的成绩，它是一个包含多个科目和成绩的JSON数组。

首先，我们需要创建一个students表并插入一些数据，示例代码如下：

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grades JSON
);

INSERT INTO students (grades) VALUES
  ('[{"subject": "Math", "score": 90}, {"subject": "English", "score": 85}]'),
  ('[{"subject": "Math", "score": 95}, {"subject": "English", "score": 80}]');

现在，我们可以使用JSON_TABLE来查询和操作这些学生的成绩。例如，我们可以使用JSON_TABLE来提取学生的科目和成绩，示例代码如下：

SELECT subject, score
FROM students, JSON_TABLE(grades, '$[*]'
  COLUMNS (
    subject VARCHAR(255) PATH '$.subject',
    score INT PATH '$.score'
  )
) AS t;

运行以上代码，我们将得到以下结果：