sql—选择嵌套json数组包含特定值的行

tp5buhyn 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(273)

我在数据库中有一些这样的数据库行 Employee 表格：

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}

我如何选择没有在comb工作过的员工？
你对此有何疑问？到目前为止，由于这个复杂的嵌套json数组，我什么都没有得到。
我正在尝试：

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'

sql JSON sql-server select sql-server-json

来源：https://stackoverflow.com/questions/62446444/select-row-where-nested-json-array-contains-certain-value

3条答案

按热度按时间

35g0bw711#

你可以试着用 EXISTS() 以及 OPENJSON() .
表格：

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

声明：

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

赞(0）回复(0）举报 2021-07-26

5lwkijsr2#

一种方法是在 HAVING 条款`：

SELECT V.EmpId,
       V.Name
FROM (VALUES(1,'Eric',N'{"Experience": [{"Title": "Assistant Accountant", "Company": "ComA", "YearsOfExperience": 3}, {"Title": "Accountant", "Company": "ComB", "YearsOfExperience": 2}], "EmployedYear":2016}'),
            (2,'John',N'{"Experience": [{"Title": "Tech Engineer", "Company": "ComX", "Years": 5}, {"Title": "Senior Tech Engineer", "Company": "ComY", "YearsOfExperience": 2}], "EmployedYear":2012}'),
            (3,'Leonard',N'{"Experience": [{"Title": "Junior Engineer", "Company": "ComJ", "Years": 2}, {"Title": "Tech Engineer", "Company": "ComB", "YearsOfExperience": "7"}], "EmployedYear":2017}'))V(EmpId,[Name],Information)
     CROSS APPLY OPENJSON(Information,'$.Experience')
     WITH (Title nvarchar(50),
           Company nvarchar(50),
           Years int) OJ
GROUP BY V.EmpId, V.Name
HAVING COUNT(CASE OJ.Company WHEN 'ComB' THEN 1 END) = 0;

赞(0）回复(0）举报 2021-07-26

cuxqih213#

你可以用 openjson() 和一个侧面连接。您不需要从嵌套对象中提取所有属性，因为您只对公司名称感兴趣：

select e.empId, e.Name
from employee e
where exists (
    select 1
    from openjson(information, '$.Experience') with(company nvarchar(max) '$.Company')
    where company = 'ComB'
)

db小提琴演示：

empId | Name   
----: | :------
    1 | Eric   
    3 | Leonard

赞(0）回复(0）举报 2021-07-26

我来回答

sql—选择嵌套json数组包含特定值的行

3条答案

相关问题

热门标签

最新问答