需求描述

公司老系统设计接口请求的响应结果放到了一个表里的一个字段中，是一个 json 格式的数据，需要解析里面的某个字段的值，如：

{
  "Content": {
    "prePactNo": "20191217003209329291",
    "dealSts": "1",
    "dealDesc": "自动审批通过"
  },
  "IsSuccess": true,
  "Message": null,
  "RespCode": "0000",
  "RespDesc": "通信成功"
}

ResponseJson 存储的就是上面说的 json 格式的数据，需要解析出 dealDesc 的值。

可以随便用一种程序语言来写一个程序来解析，比如 java、c# 等，但是如果只能用 sql 实现呢？

实现方案一

可以使用 SQL Server 内置的函数 JSON_VALUE ，直接提取 JSON 数据中的标量值。

SELECT top 10 ResponseJson,
    JSON_VALUE(ResponseJson, '$.Content.dealDesc') AS dealDesc
FROM
    business.RequestLog
WHERE ResponseJson IS NOT NULL
ORDER BY Id DESC;

$.Content.dealDesc 是 JSON 路径，表示提取 Content 对象中的 dealDesc 属性值。

实现方案二

使用 OPENJSON 可以将 JSON 数据展开为表格格式，适合复杂的 JSON 结构。

SELECT
    ResponseJson,
    dealDesc
FROM
    business.RequestLog
CROSS APPLY
    OPENJSON(ResponseJson, '$.Content')
    WITH (
        dealDesc NVARCHAR(100) '$.dealDesc'
    ) AS ParsedContent
WHERE ResponseJson IS NOT NULL

实现方案三

使用字符串操作，这种情况适合 SQL Server 版本较旧，不支持 JSON 函数的情况，但是这种方式不推荐，效率低且不够优雅。

SELECT
    ResponseJson,
    dealDesc
FROM
    business.RequestLog
CROSS APPLY
    OPENJSON(ResponseJson, '$.Content')
    WITH (
        dealDesc NVARCHAR(100) '$.dealDesc'
    ) AS ParsedContent
WHERE
  ResponseJson IS NOT NULL

其中 CHARINDEX 用于查找字段 dealDesc 的位置，SUBSTRING 提取指定位置的值。

实现方案四

使用基于字符串操作的解决方案，能够实现解析 JSON 字段值的功能，但在性能、可维护性和可靠性方面存在一些问题和需要改进的地方。

这种方式不推荐。

实现 SQL 如下：

SELECT TOP 10 w.ResponseJson,
       SUBSTRING(w.ResponseJson, p1.number + 13, p2.number-p1.number-13) AS Resul,p1.number,p2.number
FROM business.RequestLog w WITH (NOLOCK)
    JOIN
    (
        SELECT number
        FROM master.dbo.spt_values
        WHERE type = 'P'
              AND number > 0
    ) p1
        ON CHARINDEX(',"dealDesc":"', w.ResponseJson) = p1.number
		 JOIN
    (
        SELECT number
        FROM master.dbo.spt_values
        WHERE type = 'P'
              AND number > 0
    ) p2
        ON CHARINDEX('"},"IsSuccess":', w.ResponseJson) = p2.number
WHERE  InterfaceName = 'SinglePreAduit'
ORDER BY w.Id DESC;

得到的【Resul】就是需要的值。有了这个思路，还可以解析更多的值。

上面的 sql 用到了系统里的一个表【master.dbo.spt_values】，这是一个数字辅助表，使用了其 type 和 number 两个字段，取出的是 【type = 'P' AND number > 0】的数据，在数据库中执行下：

其 number 就是从1到2047连续的数字，因为一个 josn 格式是固定的，结合这个特性，再利用这个数字(即字符串的位置)我们就能建立表连接，然后取相对位置，就能取出 json 格式里的任何字段的数据了，但是缺陷是只能取到2047。

这个表就是一个辅助表，不光有我们用到了数字（type='P'），还有 bool（type='B'），有兴趣的可以去研究研究。

这种使用有性能问题，原因如下：

• 使用 CHARINDEX 和 SUBSTRING 解析字符串需要对每一行进行多次字符串操作，性能较差，尤其是在大数据量的表上。
• master.dbo.spt_values 表本身并不是为这种用途设计的，频繁 JOIN 会增加计算复杂度。

可靠性也差，原因如下：

• 硬编码的偏移量：假设了 JSON 格式固定（如 "dealDesc": 后的固定偏移量为 13），一旦数据格式发生变化（如字段名称长度变化或多余的空格），结果可能会出错。
• 边界问题：如果数据不包含 ,"dealDesc":" 或 "},"IsSuccess":，查询会失败或者结果不准确，缺少对异常情况的处理。

可读性和可维护性很差：

• SQL 过于复杂，包含多个嵌套字符串操作和硬编码，理解和维护难度较高。
• JOIN master.dbo.spt_values 表引入了额外的复杂性，对于后续的扩展或调试不够直观。

还有 master.dbo.spt_values 是 SQL Server 内置的系统表，通常不推荐直接在业务逻辑中使用，因为未来的版本可能会修改或移除。

实现方案五

可以封装一个存储过程或用户自定义函数来简化多次使用的场景。

CREATE FUNCTION dbo.GetDealDesc(@json NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN JSON_VALUE(@json, '$.Content.dealDesc');
END;
GO

调用：

SELECT 
    dbo.GetDealDesc(ResponseJson) AS dealDesc
FROM 
    business.RequestLog;