怎么在sql中解析json数据类型

SQL中轻松玩转JSON：解析JSON数据类型的实用指南**

随着Web应用的普及和NoSQL数据库的兴起,JSON（JavaScript Object Notation）已成为数据交换的通用格式，许多现代关系型数据库管理系统（RDBMS），如MySQL 5.7+、PostgreSQL、SQL Server、Oracle以及SQLite等，都内置了对JSON数据类型的支持，这使得在SQL中直接存储和操作JSON数据变得更加便捷，本文将详细介绍如何在SQL中解析JSON数据类型，帮助你高效地提取和利用其中的信息。

JSON数据类型简介

在开始解析之前,我们先简单了解一下数据库中的JSON数据类型，它不仅仅是一个简单的TEXT或VARCHAR字段，数据库提供了专门的JSON类型，用于存储、验证和操作JSON文档，这种类型通常能确保存储的数据是有效的JSON格式，并提供更高效的查询和操作能力。

为什么需要解析JSON数据？

将JSON数据存储在数据库中,我们往往需要从中提取特定的值、数组或对象进行查询、计算或展示。

• 从用户信息的JSON字段中提取用户的邮箱地址。
• 从商品的JSON属性中获取价格并筛选出特定价格区间的商品。
• 检查某个JSON对象中是否存在某个特定的键。

这就需要我们SQL中解析JSON数据的方法。

SQL中解析JSON数据的主要方法

不同数据库系统提供了不同的函数和方法来解析JSON数据,但核心思想大同小异，下面我们将以几种主流数据库为例，介绍常用的解析技巧。

MySQL / MariaDB

MySQL 5.7及以上版本提供了丰富的JSON函数。

• 提取JSON值：JSON_EXTRACT(json_document, path)

  • 这是最常用的函数之一,用于从JSON文档中提取指定路径的值。

  • path使用符号表示根节点，表示子节点，[index]表示数组索引（从0开始）。

  • 示例：

    -- 假设有一个表 users，包含一个 json 类型的列 user_info
    -- user_info 内容示例: {"name": "张三", "age": 30, "contacts": {"email": "zhangsan@example.com", "phone": "13800138000"}, "hobbies": ["reading", "swimming"]}
    -- 提取 name
    SELECT JSON_EXTRACT(user_info, '$.name') FROM users;
    -- 结果: "张三" (返回JSON格式字符串)
    -- 提取 email
    SELECT JSON_EXTRACT(user_info, '$.contacts.email') FROM users;
    -- 结果: "zhangsan@example.com"
    -- 提取第一个爱好
    SELECT JSON_EXTRACT(user_info, '$.hobbies[0]') FROM users;
    -- 结果: "reading"

• 简化访问操作符：-> 和 ->>

  • ->：作用与JSON_EXTRACT相同，返回JSON格式的值。

  • ->>：作用与JSON_EXTRACT相同，但返回的是SQL原生数据类型（如字符串、数字、布尔值），去除了JSON引号。

  • 示例：

    -- 使用 ->
    SELECT user_info -> '$.name' FROM users; -- 返回 "张三" (带引号)
    -- 使用 ->>
    SELECT user_info ->> '$.name' FROM users; -- 返回 张三 (不带引号)
    SELECT user_info ->> '$.age' FROM users;  -- 返回 30 (数字类型)

• 检查JSON键是否存在：JSON_CONTAINS_PATH(json_document, one_or_all, path[, path])

  • 检查JSON文档中是否存在指定的路径。
  • one_or_all：可以是'ONE'（至少存在一个）或'ALL'（所有都存在）。
  • 示例：

    -- 检查是否存在 email 路径
    SELECT JSON_CONTAINS_PATH(user_info, 'ONE', '$.contacts.email') FROM users; -- 返回 1 或 0

• 查询JSON中的值：JSON_SEARCH(json_document, one_or_all, search_str[, escape_char[, path]])

  • 在JSON文档中搜索指定的字符串,并返回其路径。
  • 示例：

    -- 搜索值为 'zhangsan@example.com' 的路径
    SELECT JSON_SEARCH(user_info, 'one', 'zhangsan@example.com') FROM users;
    -- 结果: "$.contacts.email"

• 修改JSON数据：JSON_SET(json_document, path, value[, path, value]), JSON_INSERT, JSON_REPLACE, JSON_REMOVE

  虽然本文重点在解析,但了解这些修改函数有助于更灵活地处理JSON。

PostgreSQL

PostgreSQL对JSON的支持更为原生和强大,有json和jsonb两种类型（jsonb是二进制存储，查询效率更高）。

• 使用 -> 和 ->> 操作符（与MySQL类似）

  • ->：提取JSON对象或数组元素，结果为json或jsonb类型。

  • ->>：提取JSON对象或数组元素，结果为text类型。

  • 示例：

    -- 假设表 users，列 user_info (jsonb 类型)
    -- user_info 内容: {"name": "李四", "age": 25, "contacts": {"email": "lisi@example.com", "phone": "13900139000"}, "hobbies": ["coding", "gaming"]}
    -- 提取 name (jsonb)
    SELECT user_info -> 'name' FROM users; -- 返回 "李四" (带引号的JSON字符串)
    -- 提取 name (text)
    SELECT user_info ->> 'name' FROM users; -- 返回 李四
    -- 提取 email
    SELECT (user_info -> 'contacts') ->> 'email' FROM users; -- 返回 lisi@example.com

• 使用 #> 和 #>> 操作符（嵌套路径访问）

  • #>：通过路径数组提取JSON对象或数组元素，结果为json或jsonb。

  • #>>：通过路径数组提取JSON对象或数组元素，结果为text。

  • 示例：

    -- 使用路径数组提取 email
    SELECT user_info #> '{contacts, email}' FROM users; -- 返回 "lisi@example.com" (jsonb)
    SELECT user_info #>> '{contacts, email}' FROM users; -- 返回 lisi@example.com (text)
    -- 提取第一个爱好
    SELECT user_info #> '{hobbies, 0}' FROM users; -- 返回 "coding"

• 使用 jsonb_extract_path 和 jsonb_extract_path_text 函数

  • 这些函数是#>和#>>的函数形式，更易于在动态SQL或复杂表达式中使用。
  • 示例：

    SELECT jsonb_extract_path(user_info, 'contacts', 'email') FROM users;
    SELECT jsonb_extract_path_text(user_info, 'contacts', 'email') FROM users;

• 使用 @>（包含）、<@（被包含）、（存在键）等操作符进行JSON查询

  • 示例：

    -- 检查 user_info 是否包含键 "name"
    SELECT user_info ? 'name' FROM users; -- 返回 t 或 f
    -- 检查 user_info 是否包含键 "email" 且在 "contacts" 对象下
    SELECT user_info ? 'contacts' ? 'email' FROM users;
    -- 检查 user_info 是否包含 {"age": 25} 这样的键值对
    SELECT user_info @> '{"age": 25}'::jsonb FROM users;

SQL Server

SQL Server 2016及以上版本开始支持JSON数据。

• 提取JSON值：JSON_VALUE(json_expression, path)

  • 从JSON字符串中提取一个标量值（字符串、数字、布尔值）。

  • 示例：

    -- 假设表 users，列 user_info (nvarchar(max) 类型，存储JSON字符串)
    -- user_info 内容: {"name": "王五", "age": 28, "contacts": {"email": "wangwu@example.com", "phone": "13700137000"}, "hobbies": ["traveling", "photography"]}
    -- 提取 name
    SELECT JSON_VALUE(user_info, '$.name') FROM users; -- 返回 王五
    -- 提取 email
    SELECT JSON_VALUE(user_info, '$.contacts.email') FROM users; -- 返回 wangwu@example.com