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'
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'
)