JSON字符串查询错误处理

在处理数据库中的JSON字符串时,可能会遇到各种问题。本文将探讨一个具体的错误案例,并提供相应的解决方案。

首先,设定了一个不允许NULL值的默认构造器,如果没有值,则默认值为空字符串。基于此,构建了一个查询语句,如下所示:

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

然而,运行此查询时,遇到了错误:

Msg 13609, Level 16, State 2, Line 36 JSON text is not properly formatted. Unexpected character '.' is found at position 0.

最初认为可能是属性名称输入错误,因为它是区分大小写的。但经过检查,输入是正确的。

进一步调查并发现了一些问题。在解释这些问题之前,将复制这个问题。为此,将创建一个简单的表并插入三条记录。

CREATE TABLE dbo.Employee_Information ( Id INT, FirstName NVARCHAR(100), LastName NVARCHAR(100), JsonData NVARCHAR(MAX) ) 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%'

观察到以下几点:

观察1:如果查询的表中包含一个谓词,并且该谓词不包括任何JSON(NVARCHAR列)字段中为空值的行,那么查询将成功执行。

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

观察2:即使使用过滤器来获取只包含有效JSON的行,执行也会成功。

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

观察3:即使使用过滤器来获取JSON字段中包含非空值的行,它也会失败。

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

观察4:如果删除记录并只保留一种类型的行(要么只有空行,要么只有非空行),查询将成功执行。

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"}}')

观察5:如果行只包含JSON字段中的空值,查询将失败。

TRUNCATE TABLE dbo.Employee_Information INSERT INTO dbo.Employee_Information ( Id, FirstName, LastName, JsonData ) VALUES (1, 'John', 'Doe', ''), (2, 'Jane', 'Doe', '') Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 Standard 6.2 (Build 9200: ) (Hypervisor)
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485