SQLSERVER,MySQL JSON字段解析

本文探讨了如何在SQL Server中使用自定义函数fn_split和fn_parsejson解析不一致的JSON字符串,同时展示了与MySQL中JSON_EXTRACT函数的使用方法。通过实例说明了如何获取特定键的值,适用于临时查询需求。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1.SQLSERVER JSON解析

背景:

最近在维护一个老的项目,发现数据库中某字段存储的JSON字符串,有些失败信息JSON格式还存在不一致,经常会查询这部分数据提供给下游,但是下游只需要错误描述信息,由于数据都是临时查询的,每次都不固定,所以在数据库中直接用函数进行进行JSON格式 并返回详细的描述信息。增加2个函数并调用

1.解析字符串函数

CREATE FUNCTION [dbo].[fn_split](@p_str VARCHAR(8000), @p_split VARCHAR(10))
RETURNS @tab TABLE(tid VARCHAR(2000))
AS
BEGIN
DECLARE @idx INT
DECLARE @len INT
SELECT @len = LEN(@p_split), @idx = CHARINDEX(@p_split, @p_str, 1)
WHILE(@idx >= 1)
BEGIN
INSERT INTO @tab SELECT LEFT(@p_str, @idx - 1)
SELECT @p_str = RIGHT(@p_str, LEN(@p_str) - @idx - @len + 1), @idx = CHARINDEX(@p_split, @p_str, 1)
END
if(@p_str <> '') INSERT INTO @tab SELECT @p_str
RETURN
END;

2.解析JSON字符串

CREATE FUNCTION [dbo].[fn_parsejson]
(@p_jsonstr VARCHAR(8000),@p_key VARCHAR(200)) 
RETURNS VARCHAR(3000)
AS      
BEGIN
DECLARE @rtnVal VARCHAR(3000);
  DECLARE @i INT;
  DECLARE @jsonkey VARCHAR(200);
  DECLARE @jsonvalue VARCHAR(1000);
  DECLARE @json VARCHAR(8000);
  DECLARE @tmprow VARCHAR(2000);
  DECLARE @tmpval VARCHAR(2000);
  
  IF(@p_jsonstr IS NOT NULL)
BEGIN
     SET @json = REPLACE(@p_jsonstr, '{', '');
     SET @json = REPLACE(@json, '}', '');
     SET @json = REPLACE(@json, '"', '');
DECLARE @json_cur CURSOR;  -- 声明外层游标
SET @json_cur = CURSOR FOR SELECT tid FROM fn_split(@json, ',');
OPEN @json_cur-- 打开游标(外层游标)
FETCH NEXT FROM @json_cur INTO @tmprow-- 提取外层游标行
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF(@tmprow IS NOT NULL)
BEGIN
SET @i = 0;
SET @jsonkey = '';
SET @jsonvalue = '';

DECLARE @str_cur CURSOR;-- 声明内层游标
SET @str_cur = CURSOR FOR SELECT tid FROM fn_split(@tmprow, ':');--第二次拆分后的游标(内层游标)
OPEN @str_cur  -- 打开游标
FETCH NEXT FROM @str_cur INTO @tmpval-- 提取内层游标行

WHILE(@@FETCH_STATUS = 0)
BEGIN
IF(@i = 0) 
BEGIN
SET @jsonkey = @tmpval
END
IF(@i = 1)
BEGIN
SET @jsonvalue = @tmpval
END

SET @i = @i + 1

FETCH NEXT FROM @str_cur into @tmpval-- 内层游标下移一行
END

CLOSE @str_cur-- 关闭内层游标
DEALLOCATE @str_cur -- 释放内层游标

IF(@jsonkey = @p_key)
BEGIN
SET @rtnVal = @jsonvalue
END
END

FETCH NEXT FROM @json_cur INTO @tmprow-- 内层游标结束后,外层游标下移一行
END

CLOSE @json_cur-- 关闭外层游标
DEALLOCATE @json_cur-- 释放外层游标
END 
  RETURN @rtnVal  
END

3.函数调用

DECLARE @HandleResultStr NVARCHAR(128);
SET @HandleResultStr='{"response":{"content":{"code":500,"message":"数据不存在!","success":false}, "code":0}}'
SELECT [dbo].[fn_parsejson](@HandleResultStr,'message')

2.MySQL 解析JSON 字符串

SELECT remark,JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(remark,'$.response'),'$.content'),'$.message')
FROM  orm_dept WHERE id =1

image-20220520170000318

在SQL中解析和操作JSON格式的字段数据,具体方法因数据库类型而异。以下是一些主流数据库的处理方式: ### 解析JSON字段 1. **MySQL** MySQL 提供了多个函数用于处理 JSON 数据。例如,`JSON_EXTRACT()` 可以用来提取 JSON 字段中的特定值,`JSON_UNQUOTE()` 则可以去除提取值的引号。此外,`GET_JSON_OBJECT()` 函数也可以用来获取 JSON 对象中的某个键值。 ```sql SELECT JSON_UNQUOTE(JSON_EXTRACT(org_json, '$.city')) AS city FROM user_login; ``` 2. **Oracle** Oracle 从 12c 版本开始支持 JSON 数据类型,并提供了丰富的函数来解析 JSON 数据。例如,`JSON_VALUE()` 函数可以直接提取 JSON 文档中的标量值。 ```sql SELECT JSON_VALUE(org_json, '$.city') AS city FROM user_login; ``` 3. **SQL Server** SQL Server 2016 及以后版本支持 JSON 数据的操作。使用 `OPENJSON()` 函数可以将 JSON 文档转换为表格形式的数据,便于查询和分析。 ```sql SELECT value AS city FROM user_login CROSS APPLY OPENJSON(org_json, '$.city'); ``` ### 关联JSON字段 当需要根据 JSON 字段与其他表进行关联时,可以通过解析 JSON 字段后得到的具体值来进行连接。 1. **MySQL 示例** ```sql SELECT * FROM gxy_school AS s JOIN user_login AS u ON s.province = JSON_UNQUOTE(JSON_EXTRACT(u.org_json, '$.province')); ``` 2. **Oracle 示例** ```sql SELECT * FROM gxy_school AS s JOIN user_login AS u ON s.province = JSON_VALUE(u.org_json, '$.province'); ``` 3. **SQL Server 示例** ```sql SELECT * FROM gxy_school AS s JOIN user_login AS u ON s.province = (SELECT value FROM OPENJSON(u.org_json, '$.province')); ``` ### 操作JSON字段 除了基本的解析功能外,不同的数据库还提供了更新、添加或删除 JSON 字段内部数据的功能。 1. **MySQL** 使用 `JSON_SET()`, `JSON_REPLACE()`, 和 `JSON_REMOVE()` 等函数可以修改 JSON 字段的内容。 ```sql UPDATE user_login SET org_json = JSON_SET(org_json, '$.city', 'New York') WHERE id = 1; ``` 2. **Oracle** Oracle 支持通过 `JSON_TRANSFORM()` 函数来更新 JSON 数据。 ```sql UPDATE user_login SET org_json = JSON_TRANSFORM(org_json, SET '$.city' = 'New York') WHERE id = 1; ``` 3. **SQL Server** SQL Server 不直接支持更新 JSON 字段内的特定属性,通常需要先将整个 JSON 文档转换为表结构,完成必要的更改后再转换回 JSON 格式。 ```sql -- 假设有一个临时表 #tempOrgJson 来存储转换后的数据 SELECT * INTO #tempOrgJson FROM OPENJSON((SELECT org_json FROM user_login WHERE id = 1), '$'); -- 更新临时表中的数据 UPDATE #tempOrgJson SET [value] = 'New York' WHERE [key] = 'city'; -- 将更新后的数据重新组合成 JSON 并更新原表 UPDATE user_login SET org_json = (SELECT * FROM #tempOrgJson FOR JSON PATH) WHERE id = 1; -- 清理临时表 DROP TABLE #tempOrgJson; ``` 以上就是在不同数据库系统中解析和操作 JSON 字段的一些基本方法。需要注意的是,在实际应用过程中,应考虑性能影响,特别是对于大型数据集来说,频繁地解析和重构 JSON 数据可能会导致性能下降[^1]。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值