Nested JSON expressions are converted to strings after a union

The following query properly nests ContactAddress as an array under the outer JSON object:

select 
    Json_Query(IsNull((
        select [Name], [Id], 
            (IsNull((
                select [Address1], [ContactId] from [ContactAddress] where [ContactAddress].[ContactId] = [Contact].[Id] for json path, include_null_values
            ), Json_Query('[]'))) [Address] 
            from [Contact] 
            where [Contact].[Name] like N'Tes%' 
        for json path, include_null_values
    ), Json_Query('[]'))) data for json path

Result:

[
    {
        "data": [
            {
                "Name": "Test 'em Joe",
                "Id": 145,
                "Address": [
                    {
                        "Address1": "1234 Testing Ln",
                        "ContactId": 145
                    }
                ]
            }
        ]
    }
]

But if I wish to use a union, SQL Server requires that the union be nested like this:

select 
    Json_Query(IsNull((
        select * from (
            select [Name], [Id], 
                (IsNull((
                    select [Address1], [ContactId] from [ContactAddress] where [ContactAddress].[ContactId] = [Contact].[Id] for json path, include_null_values
                ), Json_Query('[]'))) [Address] 
                from [Contact] 
                where [Contact].[Name] like N'Tes%' 
            union all 
            select [Name], [Id], 
                (IsNull((
                    select [Address1], [ContactId] from [ContactAddress] where [ContactAddress].[ContactId] = [Contact].[Id] for json path, include_null_values
                ), Json_Query('[]'))) [Address] 
                from [Contact]
                join [ContactAddress] on [ContactAddress].[ContactId] = [Contact].[Id] 
                    and [ContactAddress].[Address1] like N'Tes%' 
            ) _
        for json path, include_null_values
    ), Json_Query('[]'))) data for json path

But the union seem

You need to put JSON_QUERY on the outer part of a UNION otherwise the compiler doesn't understand that it's already valid JSON. You also need JSON_QUERY on the outside of an ISNULL rather than the inside.

Having said that, you can actually optimize your existing query significantly, by combining the two unions.

You should also use short table aliases, and don't quote names with [] unless absolutely necessary, because it's really hard to read otherwise.
SELECT
ISNULL((
SELECT
c.Name,
c.Id,
JSON_QUERY(
ISNULL((
SELECT
ca.Address1,
ca.ContactId
FROM ContactAddress ca
WHERE ca.ContactId = c.Id
for json path, include_null_values
),
'[]')
) Address
FROM Contact c
WHERE c.Name LIKE N'Tes%'
OR EXISTS (SELECT 1
FROM ContactAddress ca
WHERE ca.ContactId = c.Id
AND ca.Address1 LIKE N'Tes%'
)
for json path, include_null_values
),
'{"data":[]}'
);


Another option, which might be more efficient as it only queries ContactAddress once
SELECT
ISNULL((
SELECT
c.Name,
c.Id,
JSON_QUERY(ISNULL(a.Address, '[]')) Address
FROM Contact c
OUTER APPLY (
SELECT
ca.Address1,
ca.ContactId
FROM ContactAddress ca
WHERE ca.ContactId = c.Id
for json path, include_null_values
) a(Address)
WHERE c.Name LIKE N'Tes%'
OR EXISTS (SELECT 1
FROM OPENJSON(a.Address)
WITH (Address1 nvarchar(100)) j
WHERE j.Address1 LIKE N'Tes%'
)
for json path, include_null_values
),
'{"data":[]}'
);


StackExchange Database Administrators Q#326909, answer score: 4