如何在 MySQL 中使用 json_extract 提取 JSON 数据 – wiki基地

作者： /

在 MySQL 中巧用 JSON_EXTRACT：深入解析 JSON 数据提取艺术

随着现代应用对灵活数据结构的需求日益增长，关系型数据库如 MySQL 引入了对 JSON（JavaScript Object Notation）数据类型的支持，极大地拓展了其数据存储和处理能力。JSON 是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成，非常适合表示半结构化或非结构化数据。

然而，将 JSON 数据存储在数据库中仅仅是第一步。如何有效地从这些结构复杂的 JSON 字段中提取所需的数据，进行查询、过滤、排序或进一步处理，是使用 JSON 数据类型时面临的关键挑战。MySQL 提供了多种强大的内置函数来操作 JSON 数据，其中，JSON_EXTRACT() 函数及其便捷的操作符形式 -> 和 ->> 是进行数据提取的核心工具。

本文将深入探讨 JSON_EXTRACT() 函数的功能、语法、核心概念（尤其是 JSON 路径），并通过大量实例详细演示如何在各种场景下提取 JSON 数据，同时也会介绍其更简洁的操作符形式，并讨论性能优化的重要性。

1. 为什么在 MySQL 中使用 JSON？

在深入了解 JSON_EXTRACT 之前，我们先简要回顾一下在 MySQL 中使用 JSON 数据类型的优势：

  • 灵活性： JSON 字段允许存储结构不固定的数据。对于那些属性变化频繁、可选属性多或者结构层级较深的数据，使用 JSON 可以避免创建大量可能为空的列或复杂的关联表。
  • 敏捷性： 在开发初期或需求变动频繁时，直接在 JSON 字段中添加或修改属性比ALTER TABLE添加/删除列要快得多，减少了数据库 schema 变更的开销。
  • 存储效率： MySQL 的 JSON 数据类型以二进制格式存储 JSON 文档，这种格式比纯文本存储更紧凑、更高效，并且支持快速读取文档元素。
  • 内置函数支持： MySQL 提供了一整套丰富的 JSON 函数（如 JSON_SET, JSON_ARRAY_APPEND, JSON_REMOVE, JSON_MERGE_PATCH, JSON_CONTAINS, JSON_VALID 等），使得在数据库层面直接操作 JSON 数据成为可能，而无需完全依赖应用层处理。

当然，使用 JSON 也并非没有缺点，比如查询时不如传统关系型列直观、索引不如固定列高效（尽管有生成列等优化手段），但对于某些特定场景，JSON 是一个非常有吸引力的选择。

2. JSON_EXTRACT() 函数简介

JSON_EXTRACT() 函数用于从一个 JSON 文档中提取指定路径下的一个或多个值。它是 MySQL 提供的基础 JSON 查询函数之一。

  • 功能: 从 JSON 文档中定位并提取指定路径下的数据。
  • 返回类型: JSON_EXTRACT() 返回的结果是一个 JSON 值。如果提取的是一个标量值（字符串、数字、布尔值、null），它会以 JSON 字符串的形式返回，并包含双引号（对于字符串）或其他 JSON 表示形式。如果提取的是一个对象或数组，它也会以 JSON 字符串的形式返回整个子文档。这一点与 ->> 操作符返回原生类型（如非带引号的字符串、数字）不同，需要特别注意。
  • 可用版本: MySQL 5.7 版本及以上开始支持原生 JSON 数据类型和相关的 JSON 函数，包括 JSON_EXTRACT()。

3. JSON_EXTRACT() 语法

JSON_EXTRACT() 函数的基本语法如下：

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

  • json_doc: 这是一个 JSON 格式的字符串或一个 JSON 数据类型的列。它是我们要从中提取数据的源 JSON 文档。
  • path: 这是一个 JSON 路径表达式，用于指定要提取的数据在 JSON 文档中的位置。可以提供一个或多个路径。
  • [, path] ...: 可以指定多个路径，一次性提取多个不同的值。

JSON_EXTRACT() 函数根据提供的路径提取对应的值。如果提供了多个路径，它将返回一个 JSON 数组，数组中的每个元素对应于每个路径提取到的值。如果某个路径在文档中不存在，则该路径对应的值在结果中为 NULL。

4. 理解 JSON 路径 (JSON Path)

JSON 路径是使用 JSON_EXTRACT()（以及其他一些 JSON 函数）的关键。它是一种类似于 XPath 用于 XML 的表达式语言，用于定位 JSON 文档中的元素。JSON 路径由一系列路径组成部分构成，描述了如何从根部导航到目标位置。

JSON 路径语法规则：

  • 根元素: JSON 路径总是以 $ 符号开始，代表整个 JSON 文档的根。
  • 访问对象成员: 使用点号 . 后面跟着成员名称来访问 JSON 对象的属性。例如，$.name 表示根对象中的 name 属性。如果属性名包含特殊字符或空格，需要使用双引号括起来，例如 $.'order-id'。
  • 访问数组元素: 使用方括号 [] 包围数组元素的索引来访问 JSON 数组中的元素。索引从 0 开始。例如，$.items[0] 表示根对象中 items 数组的第一个元素。
  • 组合路径: 可以将点号和方括号组合起来，导航到嵌套结构中。例如，$.user.address[1].city 表示根对象中 user 对象的 address 数组的第二个元素的 city 属性。
  • 通配符 *:
    • 在对象成员名位置使用 * 表示当前对象的所有成员的值。例如，$.* 表示根对象所有属性的值（返回一个数组）。$.user.* 表示根对象中 user 对象的所有属性值。
    • 在数组索引位置使用 * 表示当前数组的所有元素。例如，$.items[*] 表示根对象中 items 数组的所有元素（返回一个数组）。
  • 路径中的多个元素: 可以使用逗号 , 在方括号 [] 中指定多个数组索引或对象键，但这通常用于 JSON_EXTRACT 的第二个及后续参数，而不是单个路径内部。在一个路径内部，* 是用来获取所有元素或成员的。
  • 路径不存在: 如果指定的路径在 JSON 文档中不存在，JSON_EXTRACT() 函数将返回 NULL。

重要提示: 在 MySQL 中，路径字符串需要用单引号 ' 括起来。

让我们通过一些例子来具体看看 JSON 路径的使用：

假设有以下 JSON 文档：

json
{
"name": "Alice",
"age": 30,
"isStudent": false,
"courses": ["Math", "Science", "History"],
"address": {
"street": "123 Main St",
"city": "Anytown",
"zip": "12345"
},
"grades": [
{"subject": "Math", "score": 95},
{"subject": "Science", "score": 88}
],
"contact": null
}

对应的 JSON 路径示例：

  • $: 整个文档本身。
  • $.name: 提取 "Alice"。
  • $.age: 提取 30。
  • $.isStudent: 提取 false。
  • $.courses: 提取 ["Math", "Science", "History"]。
  • $.courses[0]: 提取 "Math"。
  • $.address.city: 提取 "Anytown"。
  • $.grades[0].subject: 提取 "Math" (第一个成绩对象的科目)。
  • $.grades[1].score: 提取 88 (第二个成绩对象的分数)。
  • $.address.zipcode: 路径不存在，提取 NULL。
  • $.courses[*]: 提取 ["Math", "Science", "History"] (所有课程)。
  • $.address.*: 提取 ["123 Main St", "Anytown", "12345"] (所有地址属性的值)。
  • $.grades[*].score: 提取 [95, 88] (所有成绩对象的分数)。
  • $.contact: 提取 null。

5. JSON_EXTRACT() 实践示例

为了更好地演示 JSON_EXTRACT() 的用法，我们先创建一个示例表并插入一些数据。

“`sql
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
student_data JSON
);

INSERT INTO students (student_data) VALUES
(‘{
“name”: “Alice”,
“age”: 30,
“isStudent”: false,
“courses”: [“Math”, “Science”, “History”],
“address”: {
“street”: “123 Main St”,
“city”: “Anytown”,
“zip”: “12345”
},
“grades”: [
{“subject”: “Math”, “score”: 95},
{“subject”: “Science”, “score”: 88}
],
“contact”: null
}’),
(‘{
“name”: “Bob”,
“age”: 25,
“isStudent”: true,
“courses”: [“Physics”, “Chemistry”],
“address”: {
“street”: “456 Oak Ave”,
“city”: “Someville”,
“zip”: “67890”
},
“grades”: [
{“subject”: “Physics”, “score”: 92},
{“subject”: “Chemistry”, “score”: 85},
{“subject”: “Math”, “score”: 78}
]
}’),
(‘{
“name”: “Charlie”,
“age”: 22,
“isStudent”: true,
“courses”: [],
“address”: {},
“grades”: []
}’);
“`

现在，我们可以使用 JSON_EXTRACT() 来查询这些数据：

示例 1: 提取简单的标量值

提取每个学生的姓名和年龄：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.age') AS student_age
FROM students;

结果:

+----+--------------+-------------+
| id | student_name | student_age |
+----+--------------+-------------+
| 1 | "Alice" | 30 |
| 2 | "Bob" | 25 |
| 3 | "Charlie" | 22 |
+----+--------------+-------------+

注意，student_name 返回的是 "Alice"，包含双引号，因为它被作为 JSON 字符串返回。student_age 返回 30，它是一个 JSON 数字。

示例 2: 提取嵌套对象中的值

提取每个学生的城市：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.address.city') AS student_city
FROM students;

结果:

+----+--------------+--------------+
| id | student_name | student_city |
+----+--------------+--------------+
| 1 | "Alice" | "Anytown" |
| 2 | "Bob" | "Someville" |
| 3 | "Charlie" | NULL |
+----+--------------+--------------+

注意，Charlie 的 address 是一个空对象 {}, $.address.city 路径不存在，因此返回 NULL。

示例 3: 提取数组中的特定元素

提取每个学生的第一门课程：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.courses[0]') AS first_course
FROM students;

结果:

+----+--------------+--------------+
| id | student_name | first_course |
+----+--------------+--------------+
| 1 | "Alice" | "Math" |
| 2 | "Bob" | "Physics" |
| 3 | "Charlie" | NULL |
+----+--------------+--------------+

Charlie 的 courses 是一个空数组 []，索引 [0] 不存在，因此返回 NULL。

示例 4: 提取数组中对象的值

提取每个学生的第一个成绩的科目和分数：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.grades[0].subject') AS first_subject,
JSON_EXTRACT(student_data, '$.grades[0].score') AS first_score
FROM students;

结果:

+----+--------------+---------------+-------------+
| id | student_name | first_subject | first_score |
+----+--------------+---------------+-------------+
| 1 | "Alice" | "Math" | 95 |
| 2 | "Bob" | "Physics" | 92 |
| 3 | "Charlie" | NULL | NULL |
+----+--------------+---------------+-------------+

Charlie 的 grades 是一个空数组 []，索引 [0] 不存在，导致后续路径也无法访问，返回 NULL。

示例 5: 提取整个数组或对象

提取每个学生的全部课程列表或地址对象：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.courses') AS all_courses,
JSON_EXTRACT(student_data, '$.address') AS full_address
FROM students;

结果:

+----+--------------+-----------------------------+----------------------------------------------+
| id | student_name | all_courses | full_address |
+----+--------------+-----------------------------+----------------------------------------------+
| 1 | "Alice" | ["Math", "Science", "History"] | {"street": "123 Main St", "city": "Anytown", "zip": "12345"} |
| 2 | "Bob" | ["Physics", "Chemistry"] | {"street": "456 Oak Ave", "city": "Someville", "zip": "67890"} |
| 3 | "Charlie" | [] | {} |
+----+--------------+-----------------------------+----------------------------------------------+

这里，all_courses 和 full_address 都以 JSON 字符串形式返回了子文档或子结构。

示例 6: 使用通配符 *

提取所有学生的地址信息（所有键值对的值）：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.address.*') AS address_values
FROM students;

结果:

+----+--------------+-----------------------------------+
| id | student_name | address_values |
+----+--------------+-----------------------------------+
| 1 | "Alice" | ["123 Main St", "Anytown", "12345"] |
| 2 | "Bob" | ["456 Oak Ave", "Someville", "67890"] |
| 3 | "Charlie" | [] |
+----+--------------+-----------------------------------+

提取所有学生的课程（所有数组元素）：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.courses[*]') AS all_courses
FROM students;

结果:

+----+--------------+-----------------------------+
| id | student_name | all_courses |
+----+--------------+-----------------------------+
| 1 | "Alice" | ["Math", "Science", "History"] |
| 2 | "Bob" | ["Physics", "Chemistry"] |
| 3 | "Charlie" | [] |
+----+--------------+-----------------------------+

提取所有成绩的分数：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name') AS student_name,
JSON_EXTRACT(student_data, '$.grades[*].score') AS all_scores
FROM students;

结果:

+----+--------------+------------+
| id | student_name | all_scores |
+----+--------------+------------+
| 1 | "Alice" | [95, 88] |
| 2 | "Bob" | [92, 85, 78] |
| 3 | "Charlie" | [] |
+----+--------------+------------+

示例 7: 提取多个路径

一次性提取姓名、城市和第一门课程：

sql
SELECT
id,
JSON_EXTRACT(student_data, '$.name', '$.address.city', '$.courses[0]') AS extracted_data
FROM students;

结果:

+----+------------------------------------------+
| id | extracted_data |
+----+------------------------------------------+
| 1 | ["Alice", "Anytown", "Math"] |
| 2 | ["Bob", "Someville", "Physics"] |
| 3 | ["Charlie", null, null] |
+----+------------------------------------------+

当提供多个路径时，JSON_EXTRACT() 返回一个 JSON 数组，其中包含按路径顺序提取到的值。注意，提取到的值仍然是它们的 JSON 表示形式（例如，字符串带引号）。

6. -> 和 ->> 操作符：JSON_EXTRACT() 的便捷形式

MySQL 5.7 引入了 -> 和 ->> 操作符，它们是 JSON_EXTRACT() 函数的语法糖，使得 JSON 数据提取更加简洁直观。

  • column->path: 相当于 JSON_EXTRACT(column, path)。它提取指定路径下的 JSON 值，并以 JSON 字符串形式返回。
  • column->>path: 相当于 JSON_UNQUOTE(JSON_EXTRACT(column, path))。它提取指定路径下的 JSON 标量值（字符串、数字、布尔值、null），并返回其 非引用 的原生 MySQL 类型。对于字符串，会移除双引号；对于数字、布尔值、null，则直接返回对应的 MySQL 数值、TINYINT(1)、NULL。这是在查询中直接使用提取出的标量值（如用于 WHERE、ORDER BY、GROUP BY 或 SELECT 列表）时更常用的形式。

示例 8: 使用 -> 操作符

提取姓名和年龄 (与示例 1 相同的结果，但语法更简洁):

sql
SELECT
id,
student_data->'$.name' AS student_name,
student_data->'$.age' AS student_age
FROM students;

结果:

+----+--------------+-------------+
| id | student_name | student_age |
+----+--------------+-------------+
| 1 | "Alice" | 30 |
| 2 | "Bob" | 25 |
| 3 | "Charlie" | 22 |
+----+--------------+-------------+

示例 9: 使用 ->> 操作符

提取姓名为非引用字符串，年龄为数值：

sql
SELECT
id,
student_data->>'$.name' AS student_name,
student_data->>'$.age' AS student_age
FROM students;

结果:

+----+--------------+-------------+
| id | student_name | student_age |
+----+--------------+-------------+
| 1 | Alice | 30 |
| 2 | Bob | 25 |
| 3 | Charlie | 22 |
+----+--------------+-------------+

可以看到，student_name 现在是 Alice，没有双引号，是纯字符串。student_age 仍然是 30，作为一个数值类型。这是 ->> 操作符的巨大优势，因为它返回的是可以在 SQL 查询中直接使用的标量值。

示例 10: 使用 ->> 提取嵌套值和数组元素

提取城市（非引用字符串）和第一门课程（非引用字符串）：

sql
SELECT
id,
student_data->>'$.name' AS student_name,
student_data->>'$.address.city' AS student_city,
student_data->>'$.courses[0]' AS first_course
FROM students;

结果:

+----+--------------+--------------+--------------+
| id | student_name | student_city | first_course |
+----+--------------+--------------+--------------+
| 1 | Alice | Anytown | Math |
| 2 | Bob | Someville | Physics |
| 3 | Charlie | NULL | NULL |
+----+--------------+--------------+--------------+

使用 ->> 提取空对象或空数组中的元素仍然会返回 NULL，与 JSON_EXTRACT 一致。如果路径提取到的是一个非标量值（如一个对象或数组），->> 仍然会尝试解引用，结果可能是 NULL 或其 JSON 字符串表示，具体取决于 MySQL 版本和上下文，但通常不推荐对非标量值使用 ->>。

总结：JSON_EXTRACT vs -> vs ->>

  • JSON_EXTRACT(doc, path): 最原始函数，返回指定路径的 JSON 值（作为 JSON 字符串或原生类型）。
  • doc->path: JSON_EXTRACT 的简写，返回指定路径的 JSON 值（作为 JSON 字符串）。
  • doc->>path: JSON_EXTRACT 并 JSON_UNQUOTE 的简写，用于提取标量值并返回其原生 MySQL 类型。在多数需要在 SQL 中直接使用提取出的标量值时，->> 是更推荐和方便的方式。

7. 使用提取出的数据进行过滤、排序和分组

JSON_EXTRACT() 或其操作符形式的强大之处在于，你可以使用它们提取出的数据在 WHERE、ORDER BY、GROUP BY 等子句中进行操作，就像使用普通列一样。

示例 11: 在 WHERE 子句中过滤

查找年龄大于 28 岁的学生：

sql
SELECT id, student_data->>'$.name' AS student_name, student_data->>'$.age' AS age
FROM students
WHERE student_data->>'$.age' > 28; -- 注意这里使用 ->> 提取数值进行比较

结果:

+----+--------------+------+
| id | student_name | age |
+----+--------------+------+
| 1 | Alice | 30 |
+----+--------------+------+

查找居住在 ‘Anytown’ 的学生：

sql
SELECT id, student_data->>'$.name' AS student_name, student_data->>'$.address.city' AS city
FROM students
WHERE student_data->>'$.address.city' = 'Anytown'; -- 注意使用 ->> 提取字符串进行比较

结果:

+----+--------------+---------+
| id | student_name | city |
+----+--------------+---------+
| 1 | Alice | Anytown |
+----+--------------+---------+

查找学习了 ‘Math’ 课程的学生：

这里需要检查 courses 数组是否包含 ‘Math’。可以使用 JSON_CONTAINS() 函数：

“`sql
SELECT id, student_data->>’$.name’ AS student_name
FROM students
WHERE JSON_CONTAINS(student_data->’$.courses’, ‘”Math”‘); — 注意这里 JSON_CONTAINS 需要 JSON 值，所以用 -> 或 JSON_EXTRACT

— 或者，如果你确定 courses 是一个简单的字符串数组，你也可以尝试一些技巧 (效率不高):
— SELECT id, student_data->>’$.name’ AS student_name
— FROM students
— WHERE student_data->’$.courses’ LIKE ‘%”Math”%’;
“`

结果:

+----+--------------+
| id | student_name |
+----+--------------+
| 1 | Alice |
| 2 | Bob |
+----+--------------+

JSON_CONTAINS(json_doc, candidate) 函数检查 candidate 是否包含在 json_doc 中。对于数组，它检查元素是否存在；对于对象，它检查键值对是否存在（值必须匹配）。注意，candidate ' "Math" ' 需要是 JSON 字符串格式。

示例 12: 在 ORDER BY 子句中排序

按年龄对学生进行排序：

sql
SELECT id, student_data->>'$.name' AS student_name, student_data->>'$.age' AS age
FROM students
ORDER BY student_data->>'$.age'; -- 使用 ->> 提取数值进行排序

结果:

+----+--------------+------+
| id | student_name | age |
+----+--------------+------+
| 3 | Charlie | 22 |
| 2 | Bob | 25 |
| 1 | Alice | 30 |
+----+--------------+------+

示例 13: 在 GROUP BY 子句中分组

按城市对学生进行分组计数：

sql
SELECT student_data->>'$.address.city' AS city, COUNT(*) AS student_count
FROM students
GROUP BY student_data->>'$.address.city'; -- 使用 ->> 提取字符串进行分组

结果:

+-----------+-------------+
| city | student_count |
+-----------+-------------+
| Anytown | 1 |
| Someville | 1 |
| NULL | 1 |
+-----------+-------------+

注意，Charlie 的城市是 NULL，被分到了一组。

8. 性能考虑与索引

直接在大型 JSON 文档上使用 JSON_EXTRACT() 或操作符进行查询过滤和排序，可能会导致性能问题。这是因为 MySQL 需要读取整个 JSON 文档，然后解析它并根据路径提取值，这个过程是计算密集型的，并且通常无法有效利用传统索引。

解决方案：使用生成列 (Generated Columns)

MySQL 5.7+ 支持生成列。生成列的值是根据表中其他列计算得出的。我们可以创建一个生成列，该列的值就是从 JSON 字段中提取出的特定数据。然后，我们可以对这个生成列创建索引。

示例 14: 创建生成列并添加索引

为学生的姓名和城市创建生成列：

“`sql
— 添加姓名生成列
ALTER TABLE students
ADD COLUMN student_name VARCHAR(255) GENERATED ALWAYS AS (student_data->>’$.name’) STORED;

— 添加城市生成列
ALTER TABLE students
ADD COLUMN student_city VARCHAR(255) GENERATED ALWAYS AS (student_data->>’$.address.city’) STORED;

— 为生成列创建索引
CREATE INDEX idx_student_name ON students (student_name);
CREATE INDEX idx_student_city ON students (student_city);
“`

  • GENERATED ALWAYS AS (...): 指定这是一个生成列，其值总是根据括号中的表达式计算。
  • (student_data->>'$.name'): 用于生成列值的表达式，这里是从 student_data JSON 字段中提取姓名并转换为字符串。
  • STORED: 表示生成列的值将被物理存储在表中。另一种选择是 VIRTUAL，表示值不存储，而是在读取时计算。对于需要索引的生成列，通常需要使用 STORED。

现在，当你执行基于姓名或城市的查询时，MySQL 可以使用在生成列上创建的索引，从而显著提高查询性能：

sql
-- 现在这个查询可以使用 idx_student_city 索引
SELECT id, student_data->>'$.name' AS name, student_city
FROM students
WHERE student_city = 'Anytown';

使用 EXPLAIN 命令可以验证是否使用了索引。

重要提示：

  • 并非所有 JSON 函数和表达式都可以在生成列中使用。只有那些在列值改变时能确定性计算出新值的表达式才允许。JSON_EXTRACT() 和 ->> 通常是可以的，前提是路径是固定的。
  • 为生成列添加索引会增加存储空间和数据修改（INSERT/UPDATE）的开销，因为需要计算并存储生成列的值，并在索引中维护其条目。因此，只对那些频繁用于查询过滤或排序的关键 JSON 属性创建生成列和索引。
  • 对于需要索引数组中的元素，情况稍微复杂。你可能需要结合其他方法（如反范式化、使用全文索引或将数组元素提取到关联表中）或利用 MySQL 8.0+ 的一些高级索引功能（如 Multi-Valued Indexes on JSON arrays），这些超出了 JSON_EXTRACT 本身的范畴，但值得了解。

9. 错误处理和边界情况

  • 路径不存在: 如前所述，如果 JSON_EXTRACT() 或操作符指定的路径在 JSON 文档中不存在，结果是 NULL。这通常不是错误，而是预期行为。
  • JSON 格式无效: 如果存储在 JSON 列中的数据不是有效的 JSON 格式，那么在插入或更新时通常就会报错（如果使用了 JSON 数据类型）。如果列类型是 TEXT 或 VARCHAR 存储 JSON 字符串，那么在调用 JSON_EXTRACT() 等函数时，如果遇到无效 JSON，函数可能会返回 NULL 或引发错误，具体行为取决于 MySQL 版本和错误的严重性。使用 JSON_VALID() 函数可以在使用前检查数据的有效性。
  • 提取非标量值并使用 ->>: 对对象或数组使用 ->> 操作符提取时，结果可能不是预期的。->> 主要用于提取字符串、数字、布尔值或 null 等标量值。如果路径指向一个对象或数组，->> 的行为可能不稳定或返回 NULL，应优先使用 -> 或 JSON_EXTRACT 提取整个子文档，然后根据需要使用其他 JSON 函数（如 JSON_UNQUOTE、JSON_KEYS、JSON_LENGTH 等）进一步处理。

10. 结合其他 JSON 函数使用 JSON_EXTRACT

JSON_EXTRACT अक्सर与其他 JSON 函数结合使用，以实现更复杂的 JSON 数据操作。例如：

  • JSON_UNQUOTE(JSON_EXTRACT(...)): 手动解引用提取出的字符串值（或使用 ->>）。
  • JSON_LENGTH(JSON_EXTRACT(..., '$.array_key')): 获取提取出的数组或对象的长度。
  • JSON_KEYS(JSON_EXTRACT(..., '$.object_key')): 获取提取出的对象的键。
  • JSON_CONTAINS(json_doc, JSON_EXTRACT(another_doc, '$.some_value')): 检查某个值是否存在。
  • JSON_EXTRACT(json_doc, '$.array_key[*]'): 提取数组的所有元素，返回一个新数组。
  • JSON_EXTRACT(json_doc, '$.object_key.*'): 提取对象所有成员的值，返回一个新数组。

这些组合提供了极大的灵活性来处理 JSON 数据。

11. 何时使用 JSON_EXTRACT (或其他 JSON 功能) 与何时使用关系型结构？

虽然 JSON 功能强大，但并非所有数据都适合存储在 JSON 字段中。选择 JSON 还是传统关系型结构应权衡以下因素：

  • 数据结构稳定性: 如果数据的结构非常固定且不常变化，使用传统的关系型列通常更高效且易于管理。
  • 查询模式: 如果你需要频繁地根据某个属性进行严格过滤、连接或聚合，并且这个属性总是存在的，将其存储在独立的、索引良好的关系型列中会比在 JSON 中提取并索引效率更高。
  • 数据关系: 如果数据之间存在复杂的多对多关系，传统的中间表设计通常比在 JSON 中存储数组或嵌套对象更清晰和易于维护。
  • Schema 灵活性需求: 如果你的数据结构经常变化，或者包含大量可选属性，JSON 可以提供更高的灵活性和更快的开发迭代速度。
  • 数据类型: JSON 适合存储混合类型、嵌套或列表数据。如果数据主要是简单的、单一类型的值，关系型列更直接。

通常，可以采用混合模式：将结构固定且用于频繁查询、过滤、关联的关键属性存储在独立的列中，而将那些非核心、可选、结构多变或嵌套复杂的数据存储在 JSON 字段中。JSON_EXTRACT 和相关函数是连接这两种世界的桥梁，允许你从 JSON 字段中提取数据，并将其与关系型数据一起使用。

12. 总结

JSON_EXTRACT() 函数是 MySQL 中用于从 JSON 文档中提取数据的基石。通过理解 JSON 路径的语法，我们可以精确地定位 JSON 结构中的任何元素、对象或数组。结合其更简洁的 -> 和 ->> 操作符，数据提取变得更加方便。特别是 ->> 操作符，它能直接提取标量值并转换为原生 MySQL 类型，极大地简化了在 WHERE、ORDER BY 等子句中使用 JSON 数据的过程。

然而，随着数据量的增长，直接在 JSON 字段上进行频繁提取和操作可能会带来性能瓶颈。通过利用 MySQL 的生成列功能，将经常用于查询的关键 JSON 属性提取到独立存储的生成列中并为其创建索引，是解决这一性能问题的有效方法。

掌握 JSON_EXTRACT 及其操作符，理解 JSON 路径，并结合生成列等优化手段，将使你能够充分利用 MySQL 对 JSON 的支持，有效地存储、查询和管理半结构化数据，为应用开发带来更大的灵活性和效率。在处理现代应用中日益复杂的非结构化或半结构化数据时，这些工具无疑是强大的助手。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部