SQL Server 2012解析表中JSON至视图/表遇嵌套查询问题求助

嘿，这问题我之前也踩过坑！核心矛盾在于SQL Server 2012压根没原生支持JSON解析功能——像OPENJSON、JSON_VALUE这类好用的JSON工具函数，是从SQL Server 2016才正式引入的，所以你直接套用高版本的写法肯定会报错。下面给你几个适配2012的可行方案：

方法1：纯字符串函数解析（适合结构固定的简单JSON） #

如果你的definition_JSON里的JSON格式非常规整（比如每个条目都固定包含name、userTask、id，字段顺序也不怎么变），可以用SQL自带的字符串处理函数（CHARINDEX、SUBSTRING）硬提取值。

举个例子，假设你的JSON结构是这样：

{"name":"订单审核", "userTask":{"assignee":"张三"}, "id":"task_001"}

提取字段的SQL代码可以这么写：

SELECT
    -- 提取name字段
    SUBSTRING(definition_JSON, 
              CHARINDEX('"name":"', definition_JSON) + 8,
              CHARINDEX('"', definition_JSON, CHARINDEX('"name":"', definition_JSON) + 8) - (CHARINDEX('"name":"', definition_JSON) + 8)
             ) AS name,
    -- 提取userTask下的assignee（嵌套字段示例）
    SUBSTRING(definition_JSON, 
              CHARINDEX('"assignee":"', definition_JSON) + 12,
              CHARINDEX('"', definition_JSON, CHARINDEX('"assignee":"', definition_JSON) + 12) - (CHARINDEX('"assignee":"', definition_JSON) + 12)
             ) AS userTask_assignee,
    -- 提取id字段
    SUBSTRING(definition_JSON, 
              CHARINDEX('"id":"', definition_JSON) + 6,
              CHARINDEX('"', definition_JSON, CHARINDEX('"id":"', definition_JSON) + 6) - (CHARINDEX('"id":"', definition_JSON) + 6)
             ) AS id
FROM LookupJSON

⚠️ 注意：这个方法只适合JSON结构完全固定的场景，如果JSON里有转义引号（\"）或者字段顺序乱跳，结果就会出错。

方法2：用CLR自定义函数（推荐处理复杂JSON） #

SQL Server 2012支持CLR集成，你可以用C#写一个自定义函数（借助Newtonsoft.Json库）来解析JSON，部署到数据库后就能轻松处理嵌套、复杂的JSON结构了。

步骤大概是这样：

1. 先开启SQL Server的CLR集成：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

2. 编写C#类库，引用Newtonsoft.Json，写一个解析JSON的方法：

using System;
using System.Data.SqlTypes;
using Newtonsoft.Json.Linq;

public class JSONParser
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetJSONValue(SqlString json, SqlString path)
    {
        if (json.IsNull || path.IsNull)
            return SqlString.Null;
        
        JObject jObj = JObject.Parse(json.Value);
        JToken token = jObj.SelectToken(path.Value);
        
        return token != null ? new SqlString(token.ToString()) : SqlString.Null;
    }
}

3. 把代码编译成DLL，然后在SQL Server里创建程序集和自定义函数：

CREATE ASSEMBLY JSONParserAssembly
FROM 'C:\YourFilePath\JSONParser.dll'
WITH PERMISSION_SET = SAFE;

CREATE FUNCTION dbo.GetJSONValue(@json NVARCHAR(MAX), @path NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME JSONParserAssembly.JSONParser.GetJSONValue;

4. 现在就可以用这个函数轻松提取字段了：

SELECT
    dbo.GetJSONValue(definition_JSON, '$.name') AS name,
    dbo.GetJSONValue(definition_JSON, '$.userTask') AS userTask, -- 嵌套对象会返回完整子JSON
    dbo.GetJSONValue(definition_JSON, '$.id') AS id
FROM LookupJSON

方法3：升级到SQL Server 2016+（长期最优解） #

如果你的环境允许升级，那直接升到SQL Server 2016或更高版本是最省心的——原生JSON函数能让代码简洁到飞起：

-- 提取单个值
SELECT
    JSON_VALUE(definition_JSON, '$.name') AS name,
    JSON_QUERY(definition_JSON, '$.userTask') AS userTask, -- 嵌套对象用JSON_QUERY
    JSON_VALUE(definition_JSON, '$.id') AS id
FROM LookupJSON

-- 或者用OPENJSON把JSON转成表结构
SELECT
    j.name,
    j.userTask,
    j.id
FROM LookupJSON
CROSS APPLY OPENJSON(definition_JSON)
WITH (
    name NVARCHAR(100) '$.name',
    userTask NVARCHAR(MAX) '$.userTask' AS JSON,
    id NVARCHAR(50) '$.id'
) j

至于你说的“嵌套查询报错提示需输入JSON格式数据”，大概率是误用了高版本的JSON函数，或者字符串解析时截取到了无效的JSON片段，导致SQL无法识别为合法JSON。用上面的方法应该能解决这个问题。

内容的提问来源于stack exchange，提问作者Nestalna