MySQL JSON 字段怎么查

直接回答：JSON 字段取值用 JSON_EXTRACT 或 -> 操作符，去掉结果里的引号用 JSON_UNQUOTE 或 ->>；注意在字段上做函数运算会导致索引失效，条件里尽量用 JSON_CONTAINS 这类可优化的写法。

在 MySQL 中，对 JSON 数据类型字段进行查询可以利用 MySQL 提供的 JSON 函数。这些函数允许你在 JSON 文档中进行各种操作，如检查值、提取数据和修改 JSON 数据。以下是一些常见的 JSON 查询操作及其对应的 SQL 语句示例。

常用 JSON 函数

JSON_CONTAINS: 检查 JSON 文档中是否包含指定的值。
JSON_EXTRACT: 从 JSON 文档中提取指定路径的值。
JSON_UNQUOTE: 去掉 JSON 字符串中的引号。
JSON_LENGTH: 获取 JSON 数组的长度。
JSON_SEARCH: 搜索 JSON 文档中是否包含特定的值，并返回其路径。

示例

假设我们有一个名为 DeviceEntity 的表，其中有一个 JSON 类型的字段 AccessType。我们要执行以下查询：

查询 JSON 数组中是否包含特定值
提取 JSON 文档中的某个值
计算 JSON 数组的长度
检查 JSON 数组是否包含某些特定值

1. 查询 JSON 数组中是否包含特定值

如果 AccessType 字段存储的是 JSON 数组，你可以使用 JSON_CONTAINS 函数来检查数组中是否包含某个值。例如，要查找包含数字 1 的记录：

sql
复制代码
SELECT *
FROM DeviceEntity
WHERE JSON_CONTAINS(AccessType, '1', '$');

在这个查询中，'1' 是我们要检查的值，'$' 是 JSON 文档的根路径。

2. 提取 JSON 文档中的某个值

使用 JSON_EXTRACT 函数从 JSON 文档中提取指定路径的值。例如，要提取 JSON 对象中的 name 字段：

sql
复制代码
SELECT JSON_EXTRACT(AccessType, '$.name') AS Name
FROM DeviceEntity;

在这个查询中，'$.name' 是 JSON 路径，用于提取 name 字段的值。

3. 计算 JSON 数组的长度

使用 JSON_LENGTH 函数来计算 JSON 数组的长度。例如，要计算 AccessType 字段中的数组长度：

sql
复制代码
SELECT JSON_LENGTH(AccessType) AS ArrayLength
FROM DeviceEntity;

这个查询返回 AccessType 字段中 JSON 数组的长度。

4. 检查 JSON 数组是否包含多个特定值

如果你需要检查 JSON 数组是否包含多个特定值，可以结合使用 JSON_CONTAINS 和 SQL 逻辑操作符。例如，要查找同时包含 1 和 2 的记录：

sql
复制代码
SELECT *
FROM DeviceEntity
WHERE JSON_CONTAINS(AccessType, '1', '$')
  AND JSON_CONTAINS(AccessType, '2', '$');

这个查询会返回同时包含 1 和 2 的记录。

总结

JSON_CONTAINS: 用于检查 JSON 数据中是否包含某个值。
JSON_EXTRACT: 用于从 JSON 数据中提取特定路径的值。
JSON_UNQUOTE: 去掉 JSON 字符串中的引号。
JSON_LENGTH: 获取 JSON 数组的长度。
JSON_SEARCH: 用于查找 JSON 文档中值的位置，并返回路径。

一段结合ef的查询例子

IQueryable<DeviceEntity> query = null;
        if (obj.AccessType != null && obj.AccessType.Count > 0)
        {
            var values = obj.AccessType;
            var jsonConditions = new StringBuilder();

            // 动态构建 JSON 查询条件
            foreach (var value in values)
            {
                if (jsonConditions.Length > 0) jsonConditions.Append(" OR ");
                jsonConditions.Append($"JSON_CONTAINS(access_type, '{value}', '$')");
            }

            var sqlQuery = $@"SELECT * FROM devices WHERE {jsonConditions}";

            query = _dbContext.Devices
                .FromSqlInterpolated(FormattableStringFactory.Create(sqlQuery));
        }

        if (query == null)
            query = _dbContext.Devices
                .OrderByDescending(o => o.CreateTime).Include(d => d.ProductEntity);
        else
            query = query.OrderByDescending(o => o.CreateTime).Include(d => d.ProductEntity);
        if (!string.IsNullOrEmpty(obj.Sn)) query = query.Where(d => d.Sn == obj.Sn);

---

这篇笔记整理自我自己的实践记录，如果做法有出入，或者你踩过别的坑，欢迎到[留言板](/message/)一起聊聊。