MySQL查询JSON内字段|极客笔记

MySQL查询JSON内字段

1. 导言

MySQL是一种常用的关系型数据库管理系统，它提供了强大的查询功能，可以对数据进行有效的存储和检索。在MySQL的新版本中，引入了对JSON数据类型的支持，使得存储和查询复杂的结构化数据变得更加方便。

本文将详细介绍如何在MySQL中查询JSON内字段的方法和技巧。我们将从以下几个方面来讨论：

JSON数据类型的简介
基本查询语法
深入查询JSON内字段
示例代码和运行结果

2. JSON数据类型的简介

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于传输和存储结构化数据。在MySQL中，JSON数据类型支持存储和操作JSON数据。一个JSON值可以是一个对象、一个数组、一个数字、一个字符串、一个布尔值或者是NULL。

MySQL提供了一系列的函数，用于处理JSON数据类型，包括创建JSON值、访问和修改JSON内字段。在查询JSON内字段之前，我们首先需要了解如何创建和存储JSON数据。

2.1 创建JSON值

在MySQL中，可以使用JSON_OBJECT函数创建一个JSON对象，具体的语法如下：

JSON_OBJECT(key1, value1, key2, value2, ...)

其中，key1、key2等表示对象的键，value1、value2等表示对象的值。例如，下面的代码创建了一个包含姓名和年龄的JSON对象：

SELECT JSON_OBJECT('name', 'John', 'age', 30);

运行结果如下：

{
  "name": "John",
  "age": 30
}

类似地，可以使用JSON_ARRAY函数创建一个JSON数组，具体的语法如下：

JSON_ARRAY(value1, value2, ...)

其中，value1、value2等表示数组的元素。例如，下面的代码创建了一个包含几个城市的JSON数组：

SELECT JSON_ARRAY('Shanghai', 'Beijing', 'Tokyo');

运行结果如下：

[
  "Shanghai",
  "Beijing",
  "Tokyo"
]

2.2 存储JSON值

在MySQL中，可以使用JSON数据类型存储JSON值。在创建表时，可以将某一列的数据类型设置为JSON，以存储JSON值。

例如，下面的代码创建了一个表users，其中有两列：id和info，其中info列的数据类型为JSON：

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

通过INSERT INTO语句，可以向表中插入JSON值。例如，下面的代码向users表插入了一条记录：

INSERT INTO users (id, info) VALUES (1, JSON_OBJECT('name', 'John', 'age', 30));

通过SELECT语句，可以查询表中的JSON值。例如，下面的代码查询了users表中的所有记录：

SELECT * FROM users;

运行结果如下：

id | info
---+-----------------------------------------------------------
1  | {"name": "John", "age": 30}

3. 基本查询语法

在MySQL中，可以使用->操作符来查询JSON内字段。具体的语法如下：

json_doc->path

其中，json_doc表示一个JSON表达式，path表示用于访问JSON内字段的路径。路径可以是一个键、或者是一个键的多层嵌套。例如，下面的代码查询了info列中的name字段：

SELECT info->'$.name' FROM users;

运行结果如下：

info->'$.name'
---------------
"John"

可以通过->操作符来连接多个键，实现对多层嵌套字段的查询。例如，下面的代码查询了info列中的name字段和age字段：

SELECT info->' $.name', info->'$ .age' FROM users;

运行结果如下：

info->' $.name' | info->'$ .age'
---------------+---------------
"John"         | 30

此外，还可以使用->>操作符来返回一个JSON字段的字符串值。例如，下面的代码查询了info列中的name字段的值：

SELECT info->>'$.name' FROM users;

运行结果如下：

info->>'$.name'
--------------
"John"

你还可以查询JSON数组的元素，使用->操作符并指定索引值。例如，下面的代码查询了info列中的第一个元素：

SELECT info->'$.[0]' FROM users;

运行结果如下：

info->'$.[0]'
------------
null

4. 深入查询JSON内字段

除了基本查询语法之外，MySQL还提供了一些函数，用于更深入地查询JSON内字段。这些函数包括JSON_CONTAINS、JSON_EXTRACT和JSON_SEARCH等。

4.1 JSON_CONTAINS函数

JSON_CONTAINS函数用于判断一个JSON数组是否包含指定的元素。它的语法如下：

JSON_CONTAINS(json_doc, value[, path])

其中，json_doc表示一个JSON表达式，value表示要查询的元素，path表示用于指定一个键的路径。如果path为空，则在整个JSON文档中搜索。

如果指定的元素存在于JSON数组中，JSON_CONTAINS函数将返回1，否则返回0。

下面的示例代码使用JSON_CONTAINS函数判断info列中的数组是否包含字符串"Shanghai"：

SELECT JSON_CONTAINS(info, '"Shanghai"') FROM users;

运行结果如下：

JSON_CONTAINS(info, '"Shanghai"')
---------------------------------
0

4.2 JSON_EXTRACT函数

JSON_EXTRACT函数用于检索JSON内字段的值。它的语法如下：

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

其中，json_doc表示一个JSON表达式，path表示用于指定键的路径。可以指定多个路径参数，每个参数对应一个键。

下面的示例代码使用JSON_EXTRACT函数查询info列中的name字段和age字段：

SELECT JSON_EXTRACT(info, ' $.name', '$ .age') FROM users;

运行结果如下：

JSON_EXTRACT(info, ' $.name', '$ .age')
--------------------------------------
["John",30]

4.3 JSON_SEARCH函数

JSON_SEARCH函数用于查询JSON内字段的路径。它的语法如下：

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

其中，json_doc表示一个JSON表达式，one_or_all表示搜索结果的返回方式，可以为'one'或者'all'，分别表示只返回第一个匹配结果和返回所有匹配结果。search_str表示要搜索的字符串，escape_char表示用于转义的字符，path表示用于指定搜索的路径。

下面的示例代码使用JSON_SEARCH函数查询info列中包含字符串"Shanghai"的路径：

SELECT JSON_SEARCH(info, 'all', '"Shanghai"') FROM users;

运行结果如下：

JSON_SEARCH(info, 'all', '"Shanghai"')
--------------------------------------
null

5. 示例代码和运行结果

下面给出一个完整的示例代码，展示了如何使用MySQL查询JSON内字段：

-- 创建表
CREATE TABLE users (
  id INT PRIMARY KEY,
  info JSON
);

-- 插入数据
INSERT INTO users (id, info) VALUES (1, JSON_OBJECT('name', 'John', 'age', 30));
INSERT INTO users (id, info) VALUES (2, JSON_OBJECT('name', 'Sarah', 'age', 25));

-- 查询数据
-- 查询name字段和age字段
SELECT info->' $.name', info->'$ .age' FROM users;

-- 使用JSON_CONTAINS函数判断数组是否包含指定元素
SELECT JSON_CONTAINS(info, '"Shanghai"') FROM users;

-- 使用JSON_EXTRACT函数检索JSON内字段的值
SELECT JSON_EXTRACT(info, ' $.name', '$ .age') FROM users;

-- 使用JSON_SEARCH函数查询JSON内字段的路径
SELECT JSON_SEARCH(info, 'all', '"Shanghai"') FROM users;

运行结果如下：

info->' $.name' | info->'$ .age'
---------------+---------------
"John"         | 30
"Sarah"        | 25

JSON_CONTAINS(info, '"Shanghai"')
---------------------------------
0
0

JSON_EXTRACT(info, ' $.name', '$ .age')
--------------------------------------
["John",30]
["Sarah",25]

JSON_SEARCH(info, 'all', '"Shanghai"')
--------------------------------------
null
null

以上示例代码演示了如何使用MySQL查询JSON内字段的基本方法和常用函数。通过这些技巧，我们可以方便地查询和操作JSON数据。在实际应用中，可以根据具体需求灵活运用这些方法，提高查询效率和数据处理能力。

6. 总结

本文详细介绍了在MySQL中查询JSON内字段的方法和技巧。我们首先对JSON数据类型进行了简要的介绍，了解了如何创建和存储JSON值。然后，我们通过基本查询语法演示了如何使用->操作符查询JSON内字段的值。接着，我们介绍了一些深入查询JSON内字段的函数，包括JSON_CONTAINS、JSON_EXTRACT和JSON_SEARCH等。最后，给出了示例代码和运行结果，帮助读者更好地理解和运用这些方法。