MySQL 如何查询JSON数组是否包含特定的值|极客笔记

MySQL 如何查询JSON数组是否包含特定的值

JSON（JavaScript Object Notation）是一种轻量级数据交换格式，常用于表示结构化的数据。MySQL 5.7引入了JSON类型，令MySQL能存储和操作JSON数据。本文将说明如何在MySQL中查询JSON数组是否包含特定的值。

阅读更多：MySQL 教程

JSON数组简介

JSON是一种基于键值对的数据结构，其中最基本的数据类型包括字符串、数字、布尔型和null。而JSON数组是一种可以容纳有序值列表的结构，它是用方括号包住、并以逗号分隔的值。

例如：

[
    "apple",
    "banana",
    "orange"
]

示例

我们假设定义了一个表 JsonData，其中有一个数据列JsonArray：

CREATE TABLE `JsonData` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `JsonArray` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

然后往JsonData中插入两条数据：

INSERT INTO `JsonData` (`JsonArray`) VALUES ('["apple","banana","orange"]'),('["lemon","pear","grape"]');

JSON数组查询通常需要应用MySQL中的一些新函数和关键词，下面会给出常用的几种方法。

1. JSON_EXTRACT和LIKE 相结合

使用 JSON_EXTRACT 函数，可以从JSON中提取出指定位置的值。但是它只能提取某个特定位置的值，不能查询是否包含某个值。所以我们认为，如果需要查询JSON数组是否包含某个值，使用LIKE代表是最佳选择之一。

在以下示例中，我们使用 JSON_EXTRACT 函数，然后使用 LIKE 声明中间是否包含特定值：

SELECT * FROM `JsonData`
WHERE JSON_EXTRACT(`JsonArray`,"$[0]") LIKE 'apple';

这个查询返回了一条记录：

1 [“apple”, “banana”, “orange”]

上述代码中使用 JSON_EXTRACT 函数将数组的第一个元素提取出来，然后使用 LIKE 声明匹配是否包含字符串“apple”。

需要注意的是，这种方式只能用于已知数组中元素位置的情况下，它无法查找一个值是否在任何位置上都存在。

2. JSON_SEARCH

MySQL提供了JSON_SEARCH函数来查找JSON中是否存在指定的值。如果指定的值不存在，函数将返回 NULL。

以下是使用JSON_SEARCH的示例：

SELECT * FROM `JsonData`
WHERE JSON_SEARCH(`JsonArray`,'one','apple') IS NOT NULL;

这个查询也返回了一条记录：

1 [“apple”, “banana”, “orange”]

其中第一个参数是查询的JSON对象，第二个参数是 ‘one’ 或 ‘all’。如果指定 ‘one’，则查询只返回一个匹配项；如果指定 ‘all’，则查询返回数组中所有匹配的项。

第三个参数是查询的“needle”，也就是需要查找的值。

JSON_SEARCH的返回值可以用于查询指定的值是否在一个数组中存在。如果返回值是 NULL，则数组中不存在给定的值；否则，返回值是数组中该项的完整路径。

例如，我们可以使用以下代码查询是否存在字符串“pear”，并且返回其完整路径。如果SQL语句返回 NULL 值，则表明该值在JSON数组中不存在。

SELECT JSON_SEARCH(`JsonArray`,'one','pear')
FROM `JsonData`;

结果中，如果查询到的指定元素存在，则返回该元素所在数组的完整路径，以斜杆（\/）分隔。如果查询到的元素存在于数组的多个位置，则返回第一个位置的路径，例如：

$[0]
$[1]
$[2]

3. JSON_CONTAINS

MySQL 5.7版本引入了 JSON_CONTAINS 函数来判断一个JSON数组是否包含某个元素。该函数使用方法如下：

SELECT * FROM `JsonData`
WHERE JSON_CONTAINS(`JsonArray`,'["pear"]', '$');

其中第一个参数是查询的JSON对象，第二个参数是需要查找的JSON对象（在这里是包含一个字符串“pear”的JSON数组），第三个参数是 $ 表示查询应该从整个JSON对象开始。

在这个例子中，我们搜索一个包含单独字符串“pear”的JSON数组。如果找到，将返回这样的行：

2 [“lemon”, “pear”, “grape”]

需要注意的是，如果我们想搜索一个不是独立字符串的值，例如数字或布尔值，需要将值转换成JSON格式。例如，如果我们想搜索数字2，在JSON格式中，需要写成2而不是原始数字2。

JSON_CONTAINS 函数同样可以用于搜索嵌套在一个对象或数组中的元素。

SELECT * FROM `JsonData`
WHERE JSON_CONTAINS(`JsonArray`,'{"name":"pear"}', "$");

这个查询将返回空结果，因为JSON数组中没有 {"name":"pear"}。

总结

这篇文章简要地介绍了使用MySQL查询JSON数组中包含某个值的三种方法，即 JSON_EXTRACT 和 LIKE、JSON_SEARCH、JSON_CONTAINS。需要注意的是，每种方法在使用时都有其自身的要求和限制，需要按照实际情况进行选择。

MySQL 如何查询JSON数组是否包含特定的值