Few days back we had a requirement to search whether a certain value exists in one of the table fields where the values are stored as JSON strings. The default constructor has been set up not to allow any NULLs but in case there’s no value, the default value has been setup as an empty string.
So basically the query would be similar to something shown below
SELECT
'x'
FROM
[schema].[TableName] AS Src
WHERE
JSON_VALUE(Src.ColumnName,'$.Root.AttributeName') LIKE 'SearchValue%'
How ever when we ran this query we got the following error
Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.
Initially we thought that we have typed the attribute incorrectly since it’s case sensitive. But in this case it was correct.
We investigated further and found out few things. But prior explaining them we will replicate this issue. For this I will create one simple table and insert three records.
--== 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','')
Now we will use the following query to find any records which the LastName is like ‘Doe’.
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.
**Note : The query will return results till the error occurs. Hence you will see some rows in your result tab in SSMS.
These are the observations we made during our investigation
Observation 01
If you query the table with a predicate and if that predicate doesn’t include any rows with blank values in the JSON (it’s an NVARCHAR column) field the query will executed successfully.
--== 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
Observation 02
Even you use a filter to fetch only rows containing a valid JSON the execution will be successful.
--== Success ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
ISJSON(E.JsonData) > 0
AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 03
Even you use a filter to fetch only rows containing a non-blank value in the JSON field, it will fail.
--== Fail ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
E.JsonData <> ''
AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 04
If you remove records and only keep either one type of rows (either only blank rows or only non-blank) the query will be executed successfully.
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%'
Observation 05
If you have rows only containing blank values in the JSON field the query will fail.
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%'
Hope this might help you if you encounter this strange behavior during your development.
Note : All the above queries are executed under the following SQL Server Version (SELECT @@VERSION)
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)
