sqljsonsql-serverselectsql-server-json

Select row where nested JSON array contains certain value


I have a few database rows like this in the Employee table:

EmpId       Name        Information
1           Eric       {“Experience”: [{“Title”: “Assistant Accountant”, “Company”: “ComA”, “YearsOfExperience”: 3}, {“Title”: “Accountant”, “Company”: “ComB”, “YearsOfExperience”: 2}], “EmployedYear”:2016}
2           John       {“Experience”: [{“Title”: “Tech Engineer”, “Company”: “ComX”, “Years”: 5}, {“Title”: “Senior Tech Engineer”, “Company”: “ComY”, “YearsOfExperience”: 2}], “EmployedYear”:2012}
3           Leonard    {“Experience”: [{“Title”: “Junior Engineer”, “Company”: “ComJ”, “Years”: 2}, {“Title”: “Tech Engineer”, “Company”: “ComB”, “YearsOfExperience”: “7”}], “EmployedYear”:2017}

How do I select employees who have not worked in ComB?

What would be query for this? So far I got nothing because of this complex nested JSON array.

I'm trying now:

SELECT Name, Id 
FROM Employee
OUTER APPLY OPENJSON(Information, '$.Experience') WITH (
   Title nvarchar(max) '$.Title', 
   Company nvarchar(max) '$.Company', 
   YearsOfExperience int '$.YearsOfExperience'
) AS [Info]
WHERE [Info].Company != 'ComB'

Solution

  • You may try to use EXISTS() and OPENJSON().

    Table:

    CREATE TABLE Employee (
        EmpId int,       
        Name varchar(100),        
        Information varchar(1000)
    )
    INSERT INTO Employee (EmpId, Name, Information)
    VALUES
        (1, 'Eric',    '{"Experience":[{"Title":"Assistant Accountant","Company":"ComA","YearsOfExperience":3},{"Title":"Accountant","Company":"ComB","YearsOfExperience":2}],"EmployedYear":2016}'),
        (2, 'John',    '{"Experience":[{"Title":"Tech Engineer","Company":"ComX","Years":5},{"Title":"Senior Tech Engineer","Company":"ComY","YearsOfExperience":2}],"EmployedYear":2012}'),
        (3, 'Leonard', '{"Experience":[{"Title":"Junior Engineer","Company":"ComJ","Years":2},{"Title":"Tech Engineer","Company":"ComB","YearsOfExperience":"7"}],"EmployedYear":2017}')
    

    Statement:

    SELECT Name, EmpId 
    FROM Employee
    WHERE NOT EXISTS (
       SELECT 1
       FROM OPENJSON(Information, '$.Experience') WITH (
         Title nvarchar(max) '$.Title', 
         Company nvarchar(max) '$.Company', 
         YearsOfExperience int '$.YearsOfExperience'
       ) AS [Info]
       WHERE [Info].Company = 'ComB'
    )