使用MySQL JSON查询筛选嵌套字段的值方式

2026-03-25 15:50:02发布 0次浏览

详情描述

MySQL 提供了多种操作和查询JSON数据的方式。以下是几种常用的JSON查询筛选方法：

1. 基本JSON路径查询

访问特定字段

-- 使用 -> 操作符（返回JSON类型）
SELECT column->'$.key' FROM table;

-- 使用 ->> 操作符（返回字符串类型）
SELECT column->>'$.key' FROM table;

-- 使用 JSON_EXTRACT() 函数
SELECT JSON_EXTRACT(column, '$.key') FROM table;

2. 查询嵌套字段

假设有以下JSON数据：

{
  "user": {
    "name": "张三",
    "age": 30,
    "address": {
      "city": "北京",
      "street": "长安街"
    }
  }
}

-- 访问嵌套字段
SELECT 
  data->'$.user.name' as name,
  data->'$.user.address.city' as city
FROM users;

-- 使用通配符 * 查询数组中的元素
SELECT data->'$.phones[*].number' FROM users;

3. WHERE条件筛选

-- 查询特定值的记录
SELECT * FROM users 
WHERE data->'$.user.name' = '"张三"';

-- 使用 ->> 避免引号问题
SELECT * FROM users 
WHERE data->>'$.user.name' = '张三';

-- 查询嵌套字段条件
SELECT * FROM users 
WHERE data->>'$.user.address.city' = '北京';

-- 数值比较
SELECT * FROM users 
WHERE CAST(data->'$.user.age' AS UNSIGNED) > 25;

4. JSON数组查询

-- 查询数组长度
SELECT JSON_LENGTH(data->'$.tags') FROM products;

-- 查询数组是否包含特定值
SELECT * FROM products 
WHERE JSON_CONTAINS(data->'$.tags', '"electronics"');

-- 查询数组是否包含路径
SELECT * FROM products 
WHERE JSON_CONTAINS_PATH(data, 'one', '$.tags');

-- 访问数组特定位置
SELECT data->'$.tags[0]' FROM products;

5. JSON函数组合使用

-- JSON_EXTRACT 与 JSON_UNQUOTE
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.user.name')) FROM users;

-- 检查JSON路径是否存在
SELECT * FROM users 
WHERE JSON_CONTAINS_PATH(data, 'one', '$.user.address.city');

-- 多个条件组合
SELECT * FROM users 
WHERE data->>'$.user.age' > '25' 
  AND data->>'$.user.address.city' = '北京';

6. 高级查询示例

-- 动态路径查询
SET @path = '$.user.address.city';
SELECT JSON_EXTRACT(data, @path) FROM users;

-- 查询所有键名
SELECT JSON_KEYS(data) FROM users;

-- 查询嵌套对象的所有键
SELECT JSON_KEYS(data->'$.user') FROM users;

-- 更新JSON字段
UPDATE users 
SET data = JSON_SET(data, '$.user.age', 31)
WHERE data->>'$.user.name' = '张三';

7. 性能优化建议

-- 创建虚拟列并建立索引
ALTER TABLE users 
ADD COLUMN user_name VARCHAR(50) 
GENERATED ALWAYS AS (data->>'$.user.name');

CREATE INDEX idx_user_name ON users(user_name);

-- 使用生成列进行查询
SELECT * FROM users WHERE user_name = '张三';

8. 完整示例

-- 创建测试表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  profile JSON
);

-- 插入数据
INSERT INTO users (profile) VALUES 
('{"name": "张三", "age": 30, "tags": ["开发者", "MySQL"]}'),
('{"name": "李四", "age": 25, "tags": ["设计师"]}');

-- 查询示例
SELECT 
  id,
  profile->>'$.name' as name,
  profile->>'$.age' as age,
  JSON_EXTRACT(profile, '$.tags[0]') as first_tag
FROM users
WHERE profile->>'$.age' > 26
  AND JSON_CONTAINS(profile->'$.tags', '"开发者"');