Select for JSON with LEFT JOIN returns single element array when no data in the right table

Question

I'm exporting some data from an MS SQL server database as JSON.

There are two tables with a foreign key from table A to table B. It is a 0-many relationship, ie. a record in table A may have some data in table B, but it doesn't have to. Table A contains people records, and table B is a list of their current and previous addresses.

I'm using this statement to get the JSON data:

SELECT
    A.Firstname,
    A.LastName,
    HistoricAddresses.AddressLine1
    HistoricAddresses.AddressLine2
FROM
    People A
LEFT JOIN
    HistoricAddresses ON HistoricAddresses.PeopleId = A.Id
FOR JSON AUTO

This results in a JSON structure that looks like this:

[
    {
        "Firstname": "xyz",
        "Lastname": "xyz",
        "HistoricAddresses": [
            {
                "AddressLine1": "abc",
                "AddressLine2": "abc"
            },
            // more historic address records follow
        ]
    },
    // more records follow
]

This is all good until one record in the A table does not have any records in the B table. In that case, the JSON looks like this:

[
    {
        "Firstname": "xyz",
        "Lastname": "xyz",
        "HistoricAddresses": [
            {}
        ]
    },
    // more records follow
]

The contents of HistoricAddresses property is an array with a single empty element. It should be just an empty array.

Is there a way to accomplish that?

Answer 1

I solved this by not using a LEFT JOIN, but a subselect.

Instead of

SELECT
    A.Firstname,
    A.LastName,
    HistoricAddresses.AddressLine1
    HistoricAddresses.AddressLine2
FROM
    People A
LEFT JOIN
    HistoricAddresses ON HistoricAddresses.PeopleId = A.Id
FOR JSON AUTO

I'm now using

SELECT
    A.Firstname,
    A.LastName,
    (SELECT 
        HistoricAddress.AddressLine1,
        HistoricAddress.AddressLine2
     FROM
        HistoricAddress
     WHERE
        HistoricAddress.PeopleId = A.Id
     FOR JSON AUTO) AS HistoricAddresses
FROM
    People A
FOR JSON AUTO

This completely leaves out the HistoricAddresses JSON property if there aren't any. My parser can work with that.

I haven't benchmarked it yet, though, so other solutions are definitely welcome.