0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sridharnetha
  • 2,104
  • 8
  • 35
  • 69

2 Answers2

2

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
Zhorov
  • 28,486
  • 6
  • 27
  • 52
1

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))
Thom A
  • 88,727
  • 11
  • 45
  • 75