我在我的表中有一个列保存了json数据:
declare @json nvarchar(max)
set @json = N'
{
"Companies": [
{
"CompanyId": "A",
"Employee": null
},
{
"CompanyId": "B",
"Employee": [
{
"EmployeePictureId": null,
"Name": "Employee1"
},
{
"EmployeePictureId": "PictureId2",
"Name": "Employee2"
}
]
},
{
"CompanyId": "C",
"Employee": [
{
"EmployeePictureId": null,
"Name": "Employee3"
},
{
"EmployeePictureId": null,
"Name": "Employee4"
}
]
}
]
}
'
是否有可能得到如下结果:
{
"EmployeePictureIds": ["PictureId2"]
}
使用Json_Query
、Json_Value
、OPENJSON
...
仅获取EmployeePictureId
并跳过空(Null)数据
顺便说一下,数组中元素的数量是不确定的。
发布于 2018-05-23 20:44:23
在SQL Server 2017中,您可以使用以下查询:
select json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE( A.EmployeePictureId , 'json')
+ '"', char(44)))) as [EmployeePictureIds]
FROM OPENJSON(@json, '$.Companies')
WITH
(
CompanyId NVARCHAR(MAX),
Employee NVARCHAR(MAX) as json
) as B
cross apply openjson (B.Employee)
with
(
EmployeePictureId VARCHAR(50),
[Name] VARCHAR(50)
) as A
where A.EmployeePictureId is not null
for json path , WITHOUT_ARRAY_WRAPPER
您提供的JSON的结果:
添加另一个非空EmployeePictureId
的结果
https://stackoverflow.com/questions/50441109
复制相似问题