如何在SQL的JSON_VALUE函数中忽略JSON字段大小写

SQL Server的JSON_VALUE函数默认对JSON字段名大小写敏感，当查询时指定的键名（如$.FactoryID）和JSON实际的字段名（如factoryID）大小写不匹配时，会返回NULL。以下是针对单值和数组场景的具体解决方案：

单值场景解决方案 #

原问题代码 #

set @FactoryID = JSON_VALUE(@JsonData,'$.FactoryID')
set @YearID = JSON_VALUE(@JsonData,'$.YearID')

方案1：通过OPENJSON解析键值对匹配 #

利用OPENJSON将JSON解析为键值对集合，通过统一转换为小写（或大写）实现忽略大小写匹配：

DECLARE @JsonData NVARCHAR(MAX) = '{
  "factoryID": "4",
  "yearID": "2",
  "loggedInUserID": "43",
  "pageIndex": "1",
  "pageSize": "10",
  "searchString": "",
  "whereString": ""
}'
DECLARE @FactoryID NVARCHAR(100), @YearID NVARCHAR(100)

-- 获取factoryID值（忽略大小写）
SELECT @FactoryID = value 
FROM OPENJSON(@JsonData)
WHERE LOWER([key]) = 'factoryid'

-- 获取yearID值（忽略大小写）
SELECT @YearID = value 
FROM OPENJSON(@JsonData)
WHERE LOWER([key]) = 'yearid'

-- 验证结果
SELECT @FactoryID AS FactoryID, @YearID AS YearID

方案2：自定义通用函数简化调用 #

创建自定义函数封装大小写忽略逻辑，后续调用更简洁：

CREATE FUNCTION dbo.JsonValueIgnoreCase(
    @Json NVARCHAR(MAX),
    @TargetKey NVARCHAR(100)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)
    SELECT @Result = value
    FROM OPENJSON(@Json)
    WHERE LOWER([key]) = LOWER(@TargetKey)
    RETURN @Result
END

调用示例：

SET @FactoryID = dbo.JsonValueIgnoreCase(@JsonData, 'FactoryID')
SET @YearID = dbo.JsonValueIgnoreCase(@JsonData, 'YearID')

数组场景解决方案 #

原问题代码 #

SELECT          
JSON_VALUE(A.value,'$.FactoryID')FactoryID,
JSON_VALUE(A.value,'$.YearID')YearID,
JSON_VALUE(A.value,'$.CreatedUserID')CreatedUserID,
JSON_VALUE(A.value,'$.ModifiedUserID')ModifiedUserID,
JSON_VALUE(A.value,'$.TempID')TempID

FROM OPENJSON(@JsonDetail) as A

方案1：嵌套OPENJSON匹配每个字段 #

对数组中的每个JSON元素，嵌套使用OPENJSON解析并忽略大小写匹配：

DECLARE @JsonDetail NVARCHAR(MAX) = '[
  {"factoryID": "4", "yearID": "2", "createdUserID": "43", "modifiedUserID": "1", "tempID": "1001"},
  {"factoryID": "5", "yearID": "3", "createdUserID": "44", "modifiedUserID": "2", "tempID": "1002"}
]'

SELECT
    (SELECT value FROM OPENJSON(A.value) WHERE LOWER([key]) = 'factoryid') AS FactoryID,
    (SELECT value FROM OPENJSON(A.value) WHERE LOWER([key]) = 'yearid') AS YearID,
    (SELECT value FROM OPENJSON(A.value) WHERE LOWER([key]) = 'createduserid') AS CreatedUserID,
    (SELECT value FROM OPENJSON(A.value) WHERE LOWER([key]) = 'modifieduserid') AS ModifiedUserID,
    (SELECT value FROM OPENJSON(A.value) WHERE LOWER([key]) = 'tempid') AS TempID
FROM OPENJSON(@JsonDetail) AS A

方案2：使用自定义函数简化代码 #

直接调用之前创建的JsonValueIgnoreCase函数，代码更简洁：

SELECT
    dbo.JsonValueIgnoreCase(A.value, 'FactoryID') AS FactoryID,
    dbo.JsonValueIgnoreCase(A.value, 'YearID') AS YearID,
    dbo.JsonValueIgnoreCase(A.value, 'CreatedUserID') AS CreatedUserID,
    dbo.JsonValueIgnoreCase(A.value, 'ModifiedUserID') AS ModifiedUserID,
    dbo.JsonValueIgnoreCase(A.value, 'TempID') AS TempID
FROM OPENJSON(@JsonDetail) AS A

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