MySQL高级数据类型之：`MySQL`的`JSON_VALUE`：其在`JSON`字段提取和类型转换中的应用。

好的，我们开始今天的讲座。今天的主题是MySQL的JSON_VALUE函数，重点探讨其在JSON字段提取和类型转换中的应用。

JSON数据类型简介

在MySQL 5.7.22及更高版本中，引入了原生的JSON数据类型，允许我们在数据库中存储和操作JSON文档。这极大地简化了处理非结构化数据的过程，避免了将JSON字符串作为普通文本处理的诸多不便。

JSON数据类型具有以下优点：

• 验证： MySQL会自动验证存储的JSON文档是否符合JSON语法。
• 优化： MySQL以优化的内部格式存储JSON数据，提高了查询效率。
• 函数支持： MySQL提供了丰富的JSON函数，用于提取、更新和操作JSON数据。

JSON_VALUE函数详解

JSON_VALUE函数是MySQL用于从JSON文档中提取标量值的关键函数。其基本语法如下：

JSON_VALUE(json_doc, path)

• json_doc: 包含JSON数据的表达式，可以是表中的JSON列，也可以是JSON字面量。
• path: JSON路径表达式，用于指定要提取的值的位置。

JSON_VALUE函数根据指定的路径提取JSON文档中的标量值，并将其转换为SQL类型。如果路径不存在或无法找到对应的值，JSON_VALUE函数将返回NULL。

JSON路径表达式

JSON路径表达式是JSON_VALUE函数的核心，它定义了如何在JSON文档中导航以找到目标值。MySQL支持以下JSON路径表达式元素：

• $: 表示JSON文档的根节点。
• .key: 表示JSON对象的键值。
• [index]: 表示JSON数组的索引（索引从0开始）。
• [*]: 表示JSON数组的所有元素。
• **.key：深度遍历，查找所有名为key的键。
• path1.path2：连接多个路径。

以下是一些JSON路径表达式的示例：

路径表达式	描述
$.name	提取根对象中名为name的键的值。
$.address.city	提取根对象中address对象中名为city的键的值。
$.hobbies[0]	提取根对象中hobbies数组的第一个元素。
$.scores[*]	提取根对象中scores数组的所有元素。
$.**.email	提取所有名为email的键的值，无论其在JSON文档中的深度。

类型转换

JSON_VALUE函数提取的值会自动转换为SQL类型。具体的转换规则如下：

• JSON字符串 -> VARCHAR
• JSON数字 -> DOUBLE
• JSON布尔值 -> TINYINT (0或1)
• JSON null -> NULL

JSON_VALUE函数的应用示例

为了更好地理解JSON_VALUE函数，我们创建一个名为users的表，其中包含一个名为profile的JSON列：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    profile JSON
);

INSERT INTO users (username, profile) VALUES
('john.doe', '{"name": "John Doe", "age": 30, "address": {"city": "New York", "country": "USA"}, "hobbies": ["reading", "hiking"]}'),
('jane.smith', '{"name": "Jane Smith", "age": 25, "address": {"city": "London", "country": "UK"}, "hobbies": ["painting", "coding"]}'),
('peter.jones', '{"name": "Peter Jones", "age": null, "address": {"city": "Sydney", "country": "Australia"}, "hobbies": ["swimming", "surfing"]}');

示例 1: 提取用户名和年龄

SELECT
    username,
    JSON_VALUE(profile, '$.name') AS name,
    JSON_VALUE(profile, '$.age') AS age
FROM
    users;

该查询从profile列中提取name和age，并将其作为name和age列返回。注意，age列的数据类型将是DOUBLE，即使JSON文档中是整数。如果age是null，则返回NULL。

示例 2: 根据城市筛选用户

SELECT
    username
FROM
    users
WHERE
    JSON_VALUE(profile, '$.address.city') = 'New York';

该查询使用JSON_VALUE函数提取address.city的值，并将其与New York进行比较。

示例 3: 提取爱好列表

SELECT
    username,
    JSON_VALUE(profile, '$.hobbies') AS hobbies
FROM
    users;

该查询提取hobbies数组，但请注意，JSON_VALUE将整个数组作为一个字符串返回。如果需要分别提取每个爱好，可以使用JSON_TABLE函数（将在后面介绍）。

示例 4: 处理NULL值

如果JSON文档中缺少某个字段，JSON_VALUE函数将返回NULL。可以使用COALESCE函数来处理NULL值：

SELECT
    username,
    COALESCE(JSON_VALUE(profile, '$.age'), 'Unknown') AS age
FROM
    users;

该查询使用COALESCE函数将age列中的NULL值替换为Unknown。

示例 5: 深度遍历查找Email

假设profile结构发生变化，Email可能位于不同层级，可以使用深度遍历查找。

CREATE TABLE users2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    profile JSON
);

INSERT INTO users2 (username, profile) VALUES
('john.doe', '{"name": "John Doe", "age": 30, "email":"[email protected]", "address": {"city": "New York", "country": "USA"}, "hobbies": ["reading", "hiking"]}'),
('jane.smith', '{"name": "Jane Smith", "age": 25, "address": {"city": "London", "country": "UK", "email":"[email protected]"}, "hobbies": ["painting", "coding"]}');

SELECT
    username,
    JSON_VALUE(profile, '$.**.email') AS email
FROM
    users2;

JSON_TABLE函数

虽然JSON_VALUE函数对于提取标量值非常有用，但它无法直接提取JSON数组中的单个元素。为了处理JSON数组，我们需要使用JSON_TABLE函数。

JSON_TABLE函数将JSON数组转换为关系表，允许我们像访问普通表一样访问数组中的元素。其基本语法如下：

JSON_TABLE(
    json_doc,
    path COLUMNS (
        column_name data_type PATH 'path_to_element',
        ...
    )
) AS alias

• json_doc: 包含JSON数据的表达式。
• path: JSON路径表达式，指定要转换为表的JSON数组。
• COLUMNS: 定义表的列及其数据类型和路径。
• column_name: 表的列名。
• data_type: 列的数据类型。
• path_to_element: JSON路径表达式，指定要提取到该列的JSON元素。
• alias: 表的别名。

示例 6: 使用JSON_TABLE提取爱好列表

SELECT
    u.username,
    h.hobby
FROM
    users u,
    JSON_TABLE(
        u.profile,
        '$.hobbies[*]' COLUMNS (
            hobby VARCHAR(255) PATH '$'
        )
    ) AS h;

该查询使用JSON_TABLE函数将hobbies数组转换为名为h的表，其中包含一个名为hobby的列。然后，它将users表与h表连接，以提取每个用户的爱好列表。

示例 7: 从嵌套JSON中提取数据

假设我们有一个更复杂的JSON结构，其中包含嵌套的JSON对象和数组。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products (product_name, details) VALUES
('Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}, "prices": [{"currency": "USD", "amount": 1200}, {"currency": "EUR", "amount": 1100}]}'),
('Smartphone', '{"brand": "Samsung", "model": "Galaxy S21", "specs": {"cpu": "Snapdragon 888", "ram": "8GB", "storage": "128GB"}, "prices": [{"currency": "USD", "amount": 800}, {"currency": "EUR", "amount": 750}]}');

要提取产品的CPU和价格（美元），可以使用以下查询：

SELECT
    p.product_name,
    JSON_VALUE(p.details, '$.specs.cpu') AS cpu,
    jt.amount AS price_usd
FROM
    products p
    CROSS JOIN JSON_TABLE(p.details, '$.prices[*]'
        COLUMNS (
            currency VARCHAR(3) PATH '$.currency',
            amount DECIMAL(10, 2) PATH '$.amount'
        )
    ) AS jt
WHERE jt.currency = 'USD';

在这个例子中，我们使用CROSS JOIN将products表与JSON_TABLE函数的结果连接起来。JSON_TABLE函数将prices数组转换为表，并提取currency和amount列。WHERE子句用于过滤出美元价格。

示例 8: JSON_VALUE与JSON_UNQUOTE结合使用

JSON_VALUE返回的值默认带有双引号。如果需要去除双引号，可以使用JSON_UNQUOTE函数。

SELECT
    username,
    JSON_UNQUOTE(JSON_VALUE(profile, '$.name')) AS name,
    JSON_VALUE(profile, '$.age') AS age
FROM
    users;

性能考量

虽然JSON数据类型和函数提供了很大的灵活性，但需要注意性能问题。

• 索引： 在JSON列上创建索引可以显著提高查询效率。MySQL支持在JSON列的特定路径上创建索引。
• 路径表达式： 复杂的JSON路径表达式可能会导致性能下降。尽量简化路径表达式，避免使用深度遍历。
• 数据类型转换： 频繁的数据类型转换可能会影响性能。尽量避免不必要的类型转换。
• 大数据量： 对于非常大的JSON文档，可能会遇到性能瓶颈。在这种情况下，可以考虑将数据分解为更小的JSON文档或使用其他数据存储方案。

JSON_EXTRACT vs. JSON_VALUE

JSON_EXTRACT 函数也可以用于从JSON文档中提取数据。与 JSON_VALUE 的主要区别在于：

• JSON_EXTRACT 返回的是 JSON 格式的值，即使提取的是标量值，也会被包装成 JSON 字符串。
• JSON_VALUE 返回的是 SQL 类型的值 (VARCHAR, DOUBLE, TINYINT, NULL)，会自动进行类型转换。

在大多数情况下，如果只需要提取标量值并进行 SQL 操作，JSON_VALUE 更加方便。如果需要保留 JSON 格式，或者需要提取复杂的 JSON 对象或数组，可以使用 JSON_EXTRACT。

总结：深入掌握JSON_VALUE，提升数据处理效率

JSON_VALUE是MySQL处理JSON数据的重要工具。理解其语法、路径表达式和类型转换规则，能够帮助我们更有效地从JSON文档中提取和操作数据。合理运用索引和优化路径表达式，可以提高查询性能，从而更好地利用JSON数据类型的优势。