Cannot use JSON_QUERY in WHERE clause for SQL Server

Question

I have a table: Employee. Schema: (name varchar, preferences nvarchar(max)). where preferences is a json formatted string.

My query (that fails) is:

select JSON_QUERY(preferences, '$.personal')
from Employee
where ISJSON(preferences) = 1
and len(JSON_QUERY(preferences, '$.personal')) > 2

However, this query works:

select JSON_QUERY(preferences, '$.personal')
from Employee
where ISJSON(preferences) = 1

Also, if I:

create table tmp(name varchar(256), personal nvarchar(max))

insert into tmp 
select name, JSON_QUERY(preferences, '$.personal') as personal
from Employee
where ISJSON(preferences) = 1

select personal
from tmp
where len(personal) > 2

Everything works fine.

Is this a SQL Server bug with JSON_QUERY() or am I doing something wrong?

(EDIT) Error message is:

JSON text is not properly formatted. Unexpected character 'n' is found at position 3.

(EDIT) Also fails:

select JSON_QUERY(preferences, '$.personal')
from Employee
where ISJSON(preferences) = 1
and JSON_QUERY(preferences, '$.personal') is not null

I suspect you want [JSON_VALUE](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017) for the `WHERE` clause you have, as that returns a scalar value. `JSON_QUERY`, according to the documentation *"Extracts an object or an array from a JSON string."* You can't use functionality like `LEN` (which requires a scalar text value) against an object/array. Without any sample data, however, I can't test this and so I have not posted an answer. — Thom A, Mar 25 '19 at 18:06
@Larnu my "preferences" key in my json string is a Json Object and not a scalar value. Basically, I might have "preferences": {} or I might have "preferences": {"key1": "value1", "key2": "value2"} etc. — mljohns89, Mar 25 '19 at 18:09
And that, therefore, is your problem. You have multiple values there, you can't use the `LEN` function against a group of values. It would be like having `AND LEN('value1','value2','value3',...) > 2` It wouldn't make sense. — Thom A, Mar 25 '19 at 18:10
The JSON_QUERY() function returns a nvarchar(max). Isn't that basically a string? The query that works that I gave in my example will return something like: preferences {} {} {key1: value1} {key2: value2} — mljohns89, Mar 25 '19 at 18:12
Also it's worth mentioning that the query works for some records but not all — mljohns89, Mar 25 '19 at 18:23
Then give examples of what does (and doesn't work). We can't see what you see, so how can we hope to replicate and troubleshoot the problem? — Thom A, Mar 25 '19 at 18:26
Added an edit: `and JSON_QUERY(preferences, '$.personal') is not null` — mljohns89, Mar 25 '19 at 18:32
Ahh. Sorry. I am recreating an example schema/query from a work database and I can't exactly c/p everything. What I can tell you is that my query that works returns 2,512 records without error. The query I pasted in Edit 2 returns 198 records with error message `JSON text is not properly formatted. Unexpected character 'n' is found at position 3.` If it's absolutely necessary, I can do some json generation/insert dummy data and paste everything here, was hoping it wouldn't come to that? — mljohns89, Mar 25 '19 at 18:40

score 1 · Answer 1 · answered Mar 25 '19 at 19:17

Found a work around;

with tmp as (
     select a.name, b.*
     from Employee a
     cross apply OPENJSON(preferences, '$.personal')
     where ISJSON(preferences) = 1
)
select t.[key]
from tmp t
where t.value is not null
and len(t.value) > 2

I'm pretty sure that the error with JSON_QUERY() is a bug in sql server. Hopefully it will get patched in an update soon :)

siggemannen · Answer 2 · 2022-12-11T22:59:20.460

It's not a bug. The thing is that SQL Server is free to rearrange your queries in any way it desires, which includes stuffing around the WHERE conditions.

where ISJSON(preferences) = 1
and len(JSON_QUERY(preferences, '$.personal')) > 2

might become:

where len(JSON_QUERY(preferences, '$.personal')) > 2
and ISJSON(preferences) = 1

Note that in general, SQL Server processes the SELECT "projection" last, so it's usually (not always though!!!) safe to do the "dangerous" operation on the top of SELECT ... as long as WHERE condition removes the incorrect values first.

You should do the dangerous condition inside a CASE WHEN (since it's guaranteed to be processed in order):

select JSON_QUERY(preferences, '$.personal')
from Employee
where 
   case when ISJSON(preferences) = 0 then 0
        When len(JSON_QUERY(preferences, '$.personal')) > 2
   THEN 1 ELSE 0 END = 1

Cannot use JSON_QUERY in WHERE clause for SQL Server

2 Answers2