跳到主要内容

MySQL JSON 使用

小林
小林
后端开发工程师, 专注Go开发、微服务和云原生

关于一些 MySQL JSON 操作的笔记

MySQL JSON 基本使用​

在MySQL中，你可以使用JSON相关的函数来过滤和操作JSON字段。以下是几个常见的JSON过滤和操作函数：

JSON_EXTRACT()​

这个函数用于从JSON文档中提取值。它接受一个JSON值和一个或多个路径参数

SELECT JSON_EXTRACT(json_column, '$.key') FROM table_name;

如果你的JSON结构更复杂，你可能需要提供更具体的路径

SELECT JSON_EXTRACT(json_column, '$.object.inner_key') FROM table_name;

JSON_VALUE()​

这个函数与JSON_EXTRACT()类似，但它会将结果转换为标量类型（如字符串、数字）

SELECT JSON_VALUE(json_column, '$.number') FROM table_name;

JSON_UNQUOTE()​

用于去除JSON字符串中的引号

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.string')) FROM table_name;

在JSON文档中搜索指定的值，并返回包含该值的键

SELECT JSON_SEARCH(json_column, 'one', 'search_value') FROM table_name;

JSON_CONTAINS()​

检查一个JSON值是否包含另一个JSON值或字符串

SELECT JSON_CONTAINS(json_column, '"search_value"', '$[*]') FROM table_name;

JSON_ARRAYAGG()​

将多行中的JSON值聚合到一个JSON数组中

SELECT JSON_ARRAYAGG(json_column) FROM table_name;

JSON_OBJECTAGG()​

将两列聚合为一个JSON对象

SELECT JSON_OBJECTAGG(key_column, value_column) FROM table_name;

JSON_INSERT()​

在现有JSON文档中插入新的键值对

UPDATE table_name SET json_column = JSON_INSERT(json_column, '$.new_key', 'new_value');

JSON_SET()​

设置或更新JSON文档中的一个或多个键的值

UPDATE table_name SET json_column = JSON_SET(json_column, '$.key', 'new_value');

JSON_REMOVE()​

从JSON文档中移除一个或多个键

UPDATE table_name SET json_column = JSON_REMOVE(json_column, '$.key');

JSON_REPLACE()​

替换JSON文档中一个或多个键的值

UPDATE table_name SET json_column = JSON_REPLACE(json_column, '$.key', 'new_value');

JSON_MERGE()​

合并两个或更多JSON文档

SELECT JSON_MERGE(json_column1, json_column2) FROM table_name;

MySQL JSON where 过滤​

在MySQL中，你可以使用 JSON_CONTAINS() 或者 JSON_EXTRACT() 函数配合 WHERE 子句来基于JSON字段的内容进行查询。以下是一些示例：

使用 JSON_CONTAINS()​

如果你想要检查一个JSON数组中是否包含某个值，可以使用 JSON_CONTAINS() 函数。例如，假设你有一个名为 products 的表，其中有一个名为 features 的JSON字段，该字段存储了一个JSON数组，如下所示：

{
  "features": ["wireless", "bluetooth", "touchscreen"]
}

要查找所有包含 "bluetooth" 特性的产品，你可以这样写查询：

SELECT * FROM products WHERE JSON_CONTAINS(features, '"bluetooth"', '$[*]');

这里的 $[*] 表示在数组的任何位置查找。

使用 JSON_EXTRACT() 和 WHERE​

如果你想根据JSON对象内的键值对进行过滤，可以使用 JSON_EXTRACT() 函数配合 WHERE 子句。例如，假设你有一个 users 表，其中有一个名为 details 的JSON字段，该字段存储了用户的一些信息，如下所示：

{
  "details": {
    "age": 30,
    "city": "New York"
  }
}

要查找所有年龄大于25岁的用户，你可以这样写查询：

SELECT * FROM users WHERE JSON_EXTRACT(details, '$.age') > 25;

使用 JSON_EXTRACT() 和 LIKE​

如果你想要根据JSON值中的字符串进行模糊匹配，可以使用 JSON_EXTRACT() 函数配合 LIKE 关键字。例如，要查找所有城市包含 "York" 的用户：

SELECT * FROM users WHERE JSON_EXTRACT(details, '$.city') LIKE '%York%';

MySQL JSON 分组​

在MySQL中，确实可以对JSON字段进行分组。这通常涉及到从JSON字段中提取特定的值，然后使用这些值来进行分组。MySQL提供了几个函数，如 JSON_EXTRACT() 或 JSON_UNQUOTE()，来帮助从JSON字段中获取数据，之后可以使用 GROUP BY 语句对数据进行分组。

以下是一个基本示例，说明如何根据JSON字段中的某个键的值进行分组：

假设你有一个名为 orders 的表，其中包含一个名为 order_details 的JSON字段，这个字段可能包含诸如订单的系列代码（seriesCode）等信息。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_details JSON NOT NULL
);

-- 插入一些示例数据
INSERT INTO orders (order_details)
VALUES
    ('{"seriesCode": "A123"}'),
    ('{"seriesCode": "B456"}'),
    ('{"seriesCode": "A123"}'),
    ('{"seriesCode": "C789"}');

要根据 seriesCode 进行分组并计算每种 seriesCode 的数量，你可以使用以下查询：

SELECT
    JSON_EXTRACT(order_details, '$.seriesCode') AS series_code,
    COUNT(*) AS count
FROM
    orders
GROUP BY
    JSON_EXTRACT(order_details, '$.seriesCode');

由于 JSON_EXTRACT() 返回的是JSON字符串，如果你想要去掉字符串两边的引号，可以使用 JSON_UNQUOTE() 函数：

SELECT
    JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.seriesCode')) AS series_code,
    COUNT(*) AS count
FROM
    orders
GROUP BY
    JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.seriesCode'));

在某些情况下，为了优化性能，你可能还会考虑创建一个虚拟列来存储从JSON中提取的值，这样可以避免每次查询时都执行 JSON_EXTRACT()。例如：

ALTER TABLE orders
ADD COLUMN series_code VARCHAR(255) VIRTUAL AS (JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.seriesCode')));

然后，你可以直接使用这个虚拟列进行分组：

SELECT
    series_code,
    COUNT(*) AS count
FROM
    orders
GROUP BY
    series_code;

提取 MySQL JSON 里的 字符串时会有 双引号如何处理​

在MySQL中，当你从JSON字段中提取字符串时，默认情况下，字符串会被包含在双引号 ("") 中。这是因为JSON规范要求字符串值必须被双引号包围。然而，在大多数情况下，我们希望在SQL查询结果中看到的是干净的、没有额外引号的文本。

为了解决这个问题，MySQL 提供了 JSON_UNQUOTE() 函数。这个函数可以接收一个JSON字符串值作为输入，并返回一个没有包围引号的普通字符串。

以下是一个示例，展示了如何使用 JSON_UNQUOTE() 函数：

假设你有一个名为 products 的表，其中有一个名为 details 的JSON字段，包含产品名称和其他信息。例如：

{
  "productName": "Widget",
  "productID": 12345
}

如果你想提取 productName 并去除包围它的双引号，可以使用以下查询：

SELECT JSON_UNQUOTE(JSON_EXTRACT(details, '$.productName')) AS product_name FROM products;

这将返回一个没有双引号的字符串，例如：

Widget

如果你正在对提取的值进行进一步的处理，比如在 WHERE 子句中进行比较或在 GROUP BY 中使用，那么使用 JSON_UNQUOTE() 是特别有用的，因为它可以确保字符串在后续的SQL操作中被正确地解释为文本，而不是作为字符串字面量。

例如，假设你想要统计每个产品的数量：

SELECT JSON_UNQUOTE(JSON_EXTRACT(details, '$.productName')) AS product_name, COUNT(*) AS count
FROM products
GROUP BY JSON_UNQUOTE(JSON_EXTRACT(details, '$.productName'));

这将返回一个没有双引号的产品名称列表及其对应的数量。在进行分组或聚合操作时，使用 JSON_UNQUOTE() 可以确保结果的准确性和一致性。

MySQL JSON 索引​

首先，假设你有一个名为 traces 的表，其中包含一个名为 data 的JSON字段。为了创建一个针对 traceId 键的索引，你可以使用以下SQL命令：

ALTER TABLE traces ADD INDEX idx_traceId (JSON_EXTRACT(data, '$.resourceSpans[*].scopeSpans[*].spans[*].traceId'));

然而，直接对一个JSON路径创建索引可能不会成功，因为JSON成员索引只支持单层的JSON键。在你给出的JSON结构中，traceId 是嵌套在多个层次下的，这使得直接创建索引变得复杂。

为了绕过这个问题，你可以创建一个虚拟列 (VIRTUAL GENERATED column)，这个虚拟列将包含你感兴趣的 traceId 的值，然后在虚拟列上创建索引。这样做可以简化JSON路径，使其更容易被索引。

以下是创建虚拟列和索引的步骤：

创建虚拟列​

ALTER TABLE traces
ADD COLUMN traceId VARCHAR(255) VIRTUAL GENERATED BY (JSON_EXTRACT(data, '$.resourceSpans[*].scopeSpans[*].spans[*].traceId'));

注意：这里的虚拟列定义可能不会像预期那样工作，因为 JSON_EXTRACT 不支持通配符 [*] 来遍历数组。你需要知道数组确切的大小或使用其他方法来处理数组。

创建索引​

CREATE INDEX idx_traceId ON traces(traceId);

但是，上述的虚拟列定义可能无法正确处理数组中的多个元素。在实际应用中，你可能需要遍历数组并提取所有 traceId 的值，这可能需要更复杂的逻辑，例如使用 JSON_TABLE 或其他方法。

对于嵌套在多个层次下的JSON数据，MySQL的索引机制可能无法直接满足需求，尤其是在处理数组时。在这样的情况下，你可能需要在应用程序层面进行一些预处理，比如在插入数据时就将 traceId 抽取出来并存储在单独的字段中，然后再对这个字段创建索引。这种方法虽然增加了存储需求，但在性能上会有显著的提升。

在处理复杂的JSON结构时，评估和测试不同的方法以找到最适合你应用场景的解决方案是很重要的。如果可能，考虑数据模型的设计，尽量将关键查询字段扁平化，以简化索引的创建和维护。

最后于 更新