数据库里json结构的如何查询

数据库中JSON结构的查询方法与技巧

在现代数据库应用中，JSON（JavaScript Object Notation）因其在数据存储灵活性、跨语言兼容性以及与前端开发天然契合等优势，已成为半结构化数据的主流选择，无论是MySQL 5.7+、PostgreSQL、SQL Server，还是MongoDB等数据库，都提供了对JSON数据的原生支持，如何高效查询JSON结构中的数据，是开发者面临的常见挑战，本文将结合主流数据库，系统介绍JSON结构的查询方法、核心函数及实用技巧。

JSON数据存储：字段与文档的选择

在正式查询前，需明确JSON数据的存储方式，不同数据库对JSON的支持存在差异：

• 关系型数据库（如MySQL、PostgreSQL）：通常将JSON作为字段类型存储在关系表中，例如MySQL的JSON字段、PostgreSQL的JSONB字段（JSONB对JSON进行了二进制存储，支持索引且查询效率更高）。
• NoSQL数据库（如MongoDB）：直接以文档形式存储JSON数据，每个文档是一个独立的JSON对象，集合（Collection）相当于关系型数据库的表。

存储方式决定了查询语法，但核心逻辑相通：定位JSON结构中的路径，提取目标值。

关系型数据库中JSON结构的查询

MySQL：JSON函数与路径表达式

MySQL 5.7+引入了丰富的JSON函数，支持对JSON字段的查询、修改和构造，核心思路是通过路径表达式定位数据,常用函数包括：

（1）查询JSON字段的值

• JSON_EXTRACT(column, path)：提取JSON字段中指定路径的值，返回JSON格式结果。

  -- 假设有一张用户表user_info，字段info存储JSON数据：{"name":"张三","age":25,"address":{"city":"北京","district":"朝阳区"}}
  SELECT JSON_EXTRACT(info, '$.name') FROM user_info; -- 返回："张三"（带引号的JSON字符串）
  SELECT JSON_EXTRACT(info, '$.address.city') FROM user_info; -- 返回："朝阳区"

  路径表达式中的表示根对象，表示层级访问（如$.address.city），[]用于访问数组（如$.hobbies[0]）。

• ->和->>：JSON_EXTRACT的简写，->返回JSON格式，->>返回原生数据类型（去引号）。

  SELECT info->'$.name' FROM user_info; -- 返回："张三"（JSON字符串）
  SELECT info->>'$.name' FROM user_info; -- 返回：张三（原生字符串）
  SELECT info->'$.age' + 1 FROM user_info; -- 报错：JSON字符串无法参与运算
  SELECT info->>'$.age' + 1 FROM user_info; -- 返回：26（原生整数，可参与运算）

（2）查询JSON数组与条件过滤

• JSON_CONTAINS(column, value, path)：检查指定路径下是否包含目标值。

  -- 假设info字段包含{"tags":["数据库","编程"]}，查询标签包含"编程"的用户
  SELECT * FROM user_info WHERE JSON_CONTAINS(info->'$.tags', '"编程"');

  注意：value需为JSON格式，字符串需加双引号（'"编程"'）。

• JSON_SEARCH(column, 'one', value, path)：查找目标值在JSON中的路径（若存在多个匹配，返回第一个）。

  SELECT JSON_SEARCH(info, 'one', '张三') FROM user_info; -- 返回："$."name"

（3）查询JSON中的嵌套对象与数组

对于嵌套结构，需逐层展开路径，查询“北京市朝阳区”的用户：

SELECT * FROM user_info WHERE info->'$.address.city'->>'$' = '北京' 
  AND info->'$.address.district'->>'$' = '朝阳区';

若JSON中包含数组（如{"hobbies":["篮球","阅读","编程"]}），可通过索引或条件查询：

-- 查询第一个爱好是"篮球"的用户
SELECT * FROM user_info WHERE JSON_EXTRACT(info->'$.hobbies', '$[0]') = '"篮球"';
-- 查询爱好包含"编程"的用户（需遍历数组）
SELECT * FROM user_info WHERE JSON_CONTAINS(info->'$.hobbies', '"编程"');

PostgreSQL：JSONB的高效查询

PostgreSQL的JSONB类型（二进制JSON）比JSON类型查询效率更高，支持索引和GIN（Generalized Inverted Index）索引，其查询语法更接近SQL标准,核心函数包括：

（1）路径查询与操作符

• ->：获取JSON对象字段（返回JSON格式），->>获取字段值（返回文本）。

  -- 假设info字段为{"name":"李四","age":30,"contact":{"email":"lisi@example.com"}}
  SELECT info->'name' FROM user_info; -- 返回："李四"（JSON字符串）
  SELECT info->>'name' FROM user_info; -- 返回：李四（文本）
  SELECT info->'contact'->>'email' FROM user_info; -- 返回：lisi@example.com

• #>和#>>：获取嵌套路径的JSON或文本，#>支持多级路径（用包裹）。

  SELECT info#>'{contact,email}' FROM user_info; -- 返回："lisi@example.com"
  SELECT info#>>'{contact,email}' FROM user_info; -- 返回：lisi@example.com

（2）条件查询与函数

• @>：包含运算符（检查JSON是否包含指定键值对）。

  -- 查询包含"age":30的用户
  SELECT * FROM user_info WHERE info @> '{"age":30}'::jsonb;

• 检查JSON是否包含指定键（适用于对象）。

  SELECT * FROM user_info WHERE info ? 'contact'; -- 查询包含contact字段的对象

• jsonb_array_elements：展开JSON数组为多行（适用于数组查询）。

  -- 假设info包含{"hobbies":["游泳","跑步"]}, 查询爱好包含"游泳"的用户
  SELECT user_id FROM user_info, jsonb_array_elements(info->'hobbies') AS hobby 
  WHERE hobby->>'$' = '游泳';

SQL Server：JSON数据的查询与转换

SQL Server 2016+支持JSON数据，通过OPENJSON函数将JSON转换为表结构,便于查询：

（1）使用OPENJSON解析JSON

• 单层JSON解析：

  -- 假设info字段为{"name":"王五","age":28}
  SELECT 
    value AS name,
    info.value('$.age', 'int') AS age
  FROM user_info
  CROSS APPLY OPENJSON(info) WITH(name nvarchar(50)) AS name;

  OPENJSON默认将JSON对象解析为key-value对，WITH子句可指定输出列的类型。

• 嵌套JSON解析：

  -- 假设info包含{"address":{"city":"上海","district":"浦东新区"}}
  SELECT 
    address_city = info.value('$.address.city', 'nvarchar(50)'),
    address_district = info.value('$.address.district', 'nvarchar(50)')
  FROM user_info;

  通过value()方法直接提取路径值，无需展开嵌套对象。

NoSQL数据库中JSON结构的查询（以MongoDB为例）

MongoDB原生存储JSON文档，查询基于BSON（二进制JSON），使用聚合管道（Aggregation Pipeline）和查询操作符实现灵活检索。

基本查询：点号表示法与操作符

MongoDB通过点号（）访问嵌套字段，语法为{ "field.subfield": value }。

// 假设集合user_info文档示例：
// { "name": "赵六", "age": 22, "address": { "city": "广州", "district": "天河区" }, "hobbies": ["音乐", "旅行"] }
// 查询城市为"广州"的用户
db.user_info.find({ "address.city": "广州" });
// 查询年龄大于20的用户
db.user_info.find({ age: { $gt: 20 } });

数组查询：索引匹配与条件过滤

• 查询数组元素：直接匹配数组值（相当于$eq）。

  // 查询爱好包含"音乐"的用户（无需关心索引位置）
  db.user_info.find