use string_split on json filter

Question

I'm trying to use the string_split function in my where clause to split the values from json filter and create IN clause. The query should return the values found, otherwise return everything.

Filter:

DECLARE @Filter NVARCHAR(MAX)
SET @Filter=N'{
    "objectName": "Object2"
}'

The following query, will do exactly what I want, if I only specify one objectName.

...
AND (c.objectName IS NULL AND JSON_VALUE(@Filter,N'$.objectName') IS NULL OR c.objectName= ISNULL(JSON_VALUE(@Filter,N'$.objectName'),c.objectName))

But if I change my Filter to contain , separated values:

SET @Filter=N'{
    "objectName": "Object1, Object2"
}'

and use string_split function to read those values, it's not liking the string_split function in the where clause. I believe my syntax is wrong.

AND (c.objectName IS NULL AND JSON_VALUE(@Filter,N'$.objectName') IS NULL OR c.objectName IN 
string_split(ISNULL(JSON_VALUE(@Filter,N'$.objectName'),c.objectName), ','))

Does json_value needs to be casted to varchar first?

STRING_SPLIT is a table value function. If you want it in the WHERE you need to include it in a sub query. — Thom A
– Thom A ♦, Commented Dec 4, 2018 at 18:45

Thom A · Accepted Answer · 2018-12-04 18:50:20Z

1

I can't test, however, this will (probably) get you what you are after:

  AND (c.objectName IS NULL
  AND  JSON_VALUE(@Filter,N'$.objectName') IS NULL 
   OR  c.objectName IN (SELECT value FROM STRING_SPLIT(ISNULL(JSON_VALUE(@Filter,N'$.objectName'),c.objectName), ',')))

Like I stated above, STRING_SPLIT is a TVF, you can't just reference it in the WHERE. It needs to be inside a subquery, or in your FROM clause.

answered Dec 4, 2018 at 18:50

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

smr5 Over a year ago

that's exactly what I did, based on your comment. Thank you, it worked!

Collectives™ on Stack Overflow

use string_split on json filter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related