JSON_VALUE 在包含空值和非空值的表中的异常行为（JSON 文本格式不正确。在位置 0 处发现意外字符“.”。）

几天前，我们有一个需求，需要在其中一个表字段中搜索某个值是否存在，这些值存储为 JSON 字符串。默认构造函数设置为不允许任何 NULL 值，但在没有值的情况下，默认值设置为一个空字符串。

因此，基本上，查询将类似于以下内容

SELECT
 'x'
FROM
 [schema].[TableName] AS Src
WHERE
 JSON_VALUE(Src.ColumnName,'$.Root.AttributeName') LIKE 'SearchValue%'

但是，当我们运行此查询时，我们得到了以下错误

最初，我们认为我们错误地键入了属性，因为它区分大小写。但实际上，它是正确的。

我们进一步调查并发现了一些事情。但在解释它们之前，我们将重现此问题。为此，我将创建一个简单的表并插入三条记录。

--== Create a table ==--
CREATE TABLE dbo.Employee_Information (
 Id    INT
 ,FirstName  NVARCHAR(100)
 ,LastName  NVARCHAR(100)
 ,JsonData  NVARCHAR(MAX)
)
 
--== Insert few rows ==--
INSERT INTO dbo.Employee_Information (
 Id
 ,FirstName
 ,LastName
 ,JsonData
)
VALUES
(1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}')
,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}')
,(3,'Luke','Skywalker','')

现在我们将使用以下查询来查找 LastName 为“Doe”的任何记录。

SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

**注意：查询将返回结果，直到发生错误。因此，您将在 SSMS 的结果选项卡中看到一些行。

这是我们在调查过程中得出的观察结果

观察 01

如果您使用谓词查询表，并且该谓词不包含 JSON 字段（它是一个 NVARCHAR 列）中的任何空值行，则查询将成功执行。

--== Success ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
 AND Id IN (1,2)
 
--== Success ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
 AND Id <> 3
 
--== Fail ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
 AND Id = 3

观察 02

即使您使用过滤器来仅获取包含有效 JSON 的行，执行也将成功。

--== Success ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 ISJSON(E.JsonData) > 0
 AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

观察 03

即使您使用过滤器来仅获取 JSON 字段中包含非空值的行，它也会失败。

--== Fail ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 E.JsonData <> ''
 AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

观察 04

如果您删除记录并仅保留一种类型的行（要么仅空行，要么仅非空行），则查询将成功执行。

TRUNCATE TABLE dbo.Employee_Information
INSERT INTO dbo.Employee_Information (
 Id
 ,FirstName
 ,LastName
 ,JsonData
)
VALUES
(1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}')
,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}')
 
--== Success ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

观察 05

如果您只有包含 JSON 字段中空值的行，则查询将失败。

TRUNCATE TABLE dbo.Employee_Information
INSERT INTO dbo.Employee_Information (
 Id
 ,FirstName
 ,LastName
 ,JsonData
)
VALUES
(1,'John','Doe','')
,(2,'Jane','Doe','')
 
 
--== Fail ==--
SELECT
 Id
FROM
 dbo.Employee_Information AS E
WHERE
 JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'

希望这能在您在开发过程中遇到这种异常行为时有所帮助。

注意：以上所有查询均在以下 SQL Server 版本下执行（SELECT @@VERSION）

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
2016 年 10 月 28 日 18:17:30
版权所有 (c) Microsoft Corporation
Developer Edition (64 位) on Windows Server 2012 Standard 6.2 <x64> (Build 9200: ) (Hypervisor)