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_EXTRACTLIKEJSON_SEARCHJSON_CONTAINS。需要注意的是,每种方法在使用时都有其自身的要求和限制,需要按照实际情况进行选择。

Camera课程

Python教程

Java教程

Web教程

数据库教程

图形图像教程

办公软件教程

Linux教程

计算机教程

大数据教程

开发工具教程