CODEWITHSUNDEEP
sql-serverjson-value
sridharnetha
sridharnetha

Reputation: 2248

JSON_VALUE is not working in where clause in SQL Server

I have JSON data in a column in my table. I am trying to apply where condition on the JSON column and fetch records.

Employee table:

enter image description here

Here is my SQL query:

SELECT ID, EMP_NAME 
FROM EMPLOYEE 
WHERE JSON_VALUE(TEAM, '$') IN (2, 3, 4, 5, 7, 10)

I am getting an empty result when I use this query. Any help on how to do this?

Upvotes: 0

Views: 8038

Answers (2)

Zhorov
Zhorov

Reputation: 29943

You need to parse the JSON in the TEAM column with OPENJSON():

Table:

CREATE TABLE EMPLOYEE (
   ID int,
   EMP_NAME varchar(50),
   TEAM varchar(1000)
)
INSERT INTO EMPLOYEE (ID, EMP_NAME, TEAM)
VALUES
   (1, 'Name1', '[2,11]'),
   (2, 'Name2', '[2,3,4,5,7,10]'),
   (3, 'Name3', NULL)

Statement:

SELECT DISTINCT e.ID, e.EMP_NAME 
FROM EMPLOYEE e
CROSS APPLY OPENJSON(e.TEAM) WITH (TEAM int '$') j
WHERE j.TEAM IN (2,3,4,5,7,10)

Result:

ID  EMP_NAME
1   Name1
2   Name2

As an additional option, if you want to get the matches as an aggregated text, you may use the following statement (SQL Server 2017 is needed):

SELECT e.ID, e.EMP_NAME, a.TEAM
FROM EMPLOYEE e
CROSS APPLY (
   SELECT STRING_AGG(TEAM, ',') AS TEAM
   FROM OPENJSON(e.TEAM) WITH (TEAM int '$')
   WHERE TEAM IN (2,3,4,5,7,10) 
) a
WHERE a.TEAM IS NOT NULL

Result:

ID  EMP_NAME TEAM
1   Name1    2
2   Name2    2,3,4,5,7,10

Upvotes: 2

Thom A
Thom A

Reputation: 95561

JSON_VALUE returns a scalar value, not a data set, which you appaer to think it would. If you run SELECT JSON_VALUE('[2,3,4,5,7,10]','$') you'll see that it returns NULL, so yes, no rows will be returned.

You need to treat the JSON like a data set, not a single value:

SELECT ID, EMP_NAME 
FROM EMPLOYEE E
WHERE EXISTS (SELECT 1
              FROM OPENJSON (E.TEAM) OJ
              WHERE OJ.Value IN (2,3,4,5,7,10))

Upvotes: 1

Related Questions