Click here to Skip to main content
15,867,959 members
Articles / Database Development / SQL Server / SQL Server 2016

Strange Behavior on JSON_VALUE When Table Contains Blank and Non-blank Values (JSON Text is Not Properly Formatted. Unexpected Character '.' is Found at Position 0.)

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
12 May 2019CPOL2 min read 22.2K   1  
Observations made for a strange behavior on JSON_VALUE when table contains blank and non-blank values

Few days ago, 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:

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

However, 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 to explaining them, we will replicate this issue. For this, I will create one simple table and insert three records.

SQL
--== 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’.

SQL
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 be executed successfully.

SQL
--== 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.

SQL
--== 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.

SQL
--== 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.

SQL
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.

SQL
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 <x64> (Build 9200: ) (Hypervisor)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
-- There are no messages in this forum --