mysql 查询jason格式数据

简单查询。

很多时候有复杂结构，我们存储为json格式，但字段很复杂。

 

假设有一个名为data的JSON字段，可以使用以下语法提取其中的值。

SELECT data->'$.key' FROM table_name;

 

使用JSON_CONTAINS函数可以过滤JSON数组中包含特定值的记录。例如，假设有一个名为data的JSON字段，其中包含一个名为tags的数组，可以使用以下语法过滤包含特定标签的记录：

SELECT JSON_LENGTH(data->'$.array_field') FROM table_name;

 

使用JSON_KEYS函数可以查询JSON字段中的键名。例如，假设有一个名为data的JSON字段，可以使用以下语法查询其键名：

SELECT JSON_KEYS(data) FROM table_name;

 

提取JSON字段的值​

• ​使用 -> 和 ->> 操作符​：

  -- 提取name字段（带引号）
  SELECT json_data->'$.name' FROM table;
  
  -- 提取name字段并去除引号
  SELECT json_data->>'$.name' FROM table;

   

• 条件查询​​根据键值过滤​

• -- 字符串比较（推荐使用->>）
  SELECT * FROM table WHERE json_data->>'$.name' = 'John';
  
  -- 数值比较
  SELECT * FROM table WHERE JSON_EXTRACT(json_data, '$.age') = 30;
  
  

  SELECT * FROM table WHERE json_data->>'$.user.address.city' = 'New York';

   

• 检查键或路径是否存在

• -- 检查是否存在email字段
  SELECT * FROM table WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.email');
  
  -- 检查多路径是否存在（all表示所有路径必须存在）
  SELECT * FROM table WHERE JSON_CONTAINS_PATH(json_data, 'all', '$.name', '$.age');

   

• 处理JSON数组​​查询数组包含特定值​：

• -- 查找tags数组包含"mysql"
  SELECT * FROM table WHERE JSON_CONTAINS(json_data->'$.tags', '"mysql"', '$');
  
  
  -- 获取第一个hobby（索引从0开始）
  SELECT json_data->>'$.hobbies[0]' FROM table;

   

• 更新JSON数据​​​：

• ​修改字段值​：
  UPDATE table SET json_data = JSON_REPLACE(json_data, '$.age', 31) WHERE id = 1;
  
  新增字段​：
  UPDATE table SET json_data = JSON_INSERT(json_data, '$.email', 'john@example.com') WHERE id = 1;
  
  
  删除字段​：
  UPDATE table SET json_data = JSON_REMOVE(json_data, '$.oldKey') WHERE id = 1;
  
  追加数组元素​：
  UPDATE table SET json_data = JSON_ARRAY_APPEND(json_data, '$.tags', 'new_tag');

   

• 创建索引优化查询

• 生成虚拟列并创建索引​：
  ALTER TABLE table
    ADD COLUMN name_virtual VARCHAR(50) AS (json_data->>'$.name'),
    ADD INDEX (name_virtual);

   

• 展开JSON数组为行（MySQL 8.0+）​

• SELECT t.id, j.tag
  FROM table t
  JOIN JSON_TABLE(
    t.json_data->'$.tags',
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')
  ) AS j;