Nest Json Array merge

Question

I have a column saved json data in my table:

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"
                }
            ]
        }
    ]
}
'

Is it posible to get the result like:

{
   "EmployeePictureIds": ["PictureId2"]
}

using the Json_Query, Json_Value, OPENJSON...

Only get EmployeePictureId and skip empty(null) data

By the way, the count of elements in array are not sure.

Andrea · Answer 1 · 2018-05-28T10:19:44.470

In SQL Server 2017 you can use the following query:

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

Results for the JSON you provided:

Results adding another non null EmployeePictureId:

Nest Json Array merge

1 Answers1