By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employee(
EmpId INTEGER NOT NULL PRIMARY KEY
,Name VARCHAR(10) NOT NULL
,Information VARCHAR(186) NOT NULL
);
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}');
INSERT INTO employee(EmpId,Name,Information) VALUES (2,'John','{"Experience": [{"Title": "Tech Engineer", "Company": "ComX", "Years": 5}, {"Title": "Senior Tech Engineer", "Company": "ComY", "YearsOfExperience": 2}], "EmployedYear":2012}');
INSERT INTO employee(EmpId,Name,Information) VALUES (3,'Leonard','{"Experience": [{"Title": "Junior Engineer", "Company": "ComJ", "Years": 2}, {"Title": "Tech Engineer", "Company": "ComB", "YearsOfExperience": "7"}], "EmployedYear":2017}');
3 rows affected
select e.empId, e.Name
from employee e
where exists (
select 1
from openjson(information, '$.Experience') with(company nvarchar(max) '$.Company')
where company = 'ComB'
)
| empId | Name |
|---|---|
| 1 | Eric |
| 3 | Leonard |