MySQL json_extract详解

概述

在MySQL 5.7.8版本之后，引入了对JSON数据类型的支持，使得MySQL能够处理和存储JSON（JavaScript Object Notation）格式的数据。同时，MySQL还提供了一系列的JSON函数，用于在JSON数据上进行各种操作和查询。本文将详细介绍MySQL中的json_extract函数，它用于从JSON值中提取指定的属性。

json_extract函数介绍

json_extract函数是MySQL中的一个用于处理JSON数据的函数。它的作用是从JSON值中按照指定路径提取出对应的值。

语法

json_extract的语法如下：

json_extract(json_doc, path[, path]...)

其中，json_doc是待处理的JSON文档，path是一个或多个用点号（.）分隔的路径，表示要提取的属性。

返回值

json_extract函数返回提取出的值，如果提取的属性不存在，则返回NULL。

示例

下面通过一个具体的示例来演示json_extract函数的使用。假设有一个名为students的表，其中包含以下数据：

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

INSERT INTO students (id, info) VALUES 
(1, '{"name": "张三", "age": 20, "major": "计算机科学"}'),
(2, '{"name": "李四", "age": 22, "major": "数据科学", "gpa": 3.9}');

我们可以使用json_extract函数提取出每位学生的姓名和专业：

SELECT
  json_extract(info, '.name') AS name,
  json_extract(info, '.major') AS major
FROM students;

运行上述查询语句，将得到以下结果：

name  |  major
--------------
张三   |  计算机科学
李四   |  数据科学

json_extract函数的路径语法

在json_extract函数中，路径参数用来指定要提取的JSON属性的位置。路径的语法与JavaScript中的访问对象属性的语法类似，支持以下几种形式：

1. 点号（.）表示当前层级的属性，例如json_extract(info, ‘$.name’)提取出info对象中的name属性。
2. 方括号（[]）表示按照指定的键值提取，例如json_extract(info, ‘[“name”]’)和json_extract(info, ‘[0]’)等价于json_extract(info, ‘$.name’)。
3. 通配符()表示提取当前层级下的所有属性，例如json_extract(info, ‘$.‘)提取出所有的属性值。

以下是一些示例，演示json_extract函数路径语法的使用：

示例1：提取指定键的值

给定以下JSON文档：

SET @json_doc = '{"name": "John", "age": 25, "department": "IT"}';

可以使用以下语句提取出name和age属性的值：

SELECT
  json_extract(@json_doc, '.name') AS name,
  json_extract(@json_doc, '.age') AS age;

运行上述查询语句，将得到以下结果：

name  |  age
------------
John  |  25

示例2：提取嵌套属性的值

给定以下JSON文档：

SET @json_doc = '{
  "name": "John",
  "age": 25,
  "department": {
    "name": "IT",
    "location": "New York"
  }
}';

可以使用以下语句提取出department对象的name属性和location属性的值：

SELECT
  json_extract(@json_doc, '.department.name') AS department_name,
  json_extract(@json_doc, '.department.location') AS department_location;

运行上述查询语句，将得到以下结果：

department_name  |  department_location
---------------------------------------
IT               |  New York

示例3：提取数组中的值

给定以下JSON文档：

SET @json_doc = '{
  "students": [
    {"name": "John", "age": 25},
    {"name": "Jane", "age": 23}
  ]
}';

可以使用以下语句提取出第一个学生的姓名和年龄：

SELECT
  json_extract(@json_doc, '.students[0].name') AS student_name,
  json_extract(@json_doc, '.students[0].age') AS student_age;

运行上述查询语句，将得到以下结果：

student_name  |  student_age
----------------------------
John          |  25

json_extract函数的高级用法

提取符合条件的属性

json_extract函数还支持在路径中使用通配符(*)来提取符合条件的多个属性。以下是一个示例，演示如何提取出年龄大于20岁的学生的姓名和年龄：

SELECT
  json_extract(info, '[*].name') AS name,
  json_extract(info, '[*].age') AS age
FROM students
WHERE json_extract(info, '$.age') > 20;

运行上述查询语句，将得到以下结果：

name  |  age
------------
李四   |  22

提取并转换为其他数据类型

json_extract函数返回的是JSON格式的数据，如果需要将其转换为其他数据类型，可以使用其他MySQL的函数来完成。例如，如果需要将年龄字段转换为整数类型，可以使用cast函数：

SELECT
  json_extract(info, '.name') AS name,
  CAST(json_extract(info, '.age') AS UNSIGNED) AS age
FROM students;

总结

json_extract函数是MySQL中用于处理JSON数据的重要函数之一。通过指定路径参数，可以从JSON值中提取出指定的属性。本文介绍了json_extract函数的使用语法以及路径语法的各种形式，并给出了一些具体的示例。