SQL Inner join with For JSON hierarchy

Question

I'm trying to join two SQL tables with inner join and then return them as JSON from my procedure.

My select statement is:

SELECT 
    @CustomerAddressesJSON =
        (SELECT              
             Address.AddressID, Address.CustomerID,
             Address.AddressTypeID, Address.IsPrimary,
             CountryID, StateID, CountyID, DistrictID,
             StreetID, StreetNumber, PostalCode,
             AdditionalInformation, AddressImageID,
             CreatedOn, CreatedBy
         FROM 
             [sCustomerManagement].[tCustomerAddresses] Address
         INNER JOIN 
             [sCustomerManagement].[tAddresses] AddressDetails ON Address.AddressID = AddressDetails.AddressID
         WHERE 
             CustomerID = @CustomerID
         FOR JSON AUTO)

and the result is like this:

"customerAddressesJSON": "[ {
  "AddressID": 1,
  "CustomerID": 1,
  "AddressTypeID": "T",
  "IsPrimary": true,
  "AddressDetails": [
    {
      "CountryID": 1,
      "StateID": 1,
      "CountyID": 1,
      "DistrictID": 1,
      "StreetID": 1,
      "StreetNumber": "125",
      "PostalCode": "1000",
      "AdditionalInformation": "Metro Sofia",
      "CreatedOn": "2017-10-24T11:46:20.1933333",
      "CreatedBy": 24
    }
  ]
}, {
  "AddressID": 2,
  "CustomerID": 1,
  "AddressTypeID": "T",
  "IsPrimary": true,
  "AddressDetails": [
    {
      "CountryID": 1,
      "StateID": 1,
      "CountyID": 1,
      "DistrictID": 1,
      "StreetID": 1,
      "StreetNumber": "125",
      "PostalCode": "1000",
      "AdditionalInformation": "Metro Sofia",
      "CreatedOn": "2017-10-24T11:46:20.1933333",
      "CreatedBy": 24
    }
  ]
}

The problem is that I don't want the information in the array AddressDetails to be nested. Is it possible the information there to be outside, so I can receive 2 flat objects, without nested information ?

Thanks

Answer 1

Consider using the PATH mode with dot syntax and map all fields to Address as discussed in docs.

SELECT 
    @CustomerAddressesJSON =
        (SELECT              
             a.AddressID AS 'Address.AddressID', a.CustomerID AS 'Address.CustomerID',
             a.AddressTypeID AS 'Address.AddressTypeID', a,IsPrimary AS 'Address.IsPrimary',
             d.CountryID AS 'Address.CountryID', d.StateID AS 'Address.StateID',
             d.CountyID AS 'Address.CountyID', d.DistrictID AS 'Address.DistrictID',
             d.StreetID As 'Address.StreetID', d.StreetNumber AS 'Address.StreetNumber', 
             d.PostalCode AS 'Address.PostalCode',
             d.AdditionalInformation AS 'Address.AdditionalInformation', 
             d.AddressImageID AS 'Address.AddressImageID',
             d.CreatedOn AS 'Address.CreatedOn', d.CreatedBy AS 'Address.CreatedBy'
         FROM 
             [sCustomerManagement].[tCustomerAddresses] a
         INNER JOIN 
             [sCustomerManagement].[tAddresses] d ON a.AddressID = d.AddressID
         WHERE 
             a.CustomerID = @CustomerID
         FOR JSON PATH)

Alternatively, use a derived table:

SELECT 
    @CustomerAddressesJSON =
        (SELECT m.* 
         FROM 
             (SELECT a.AddressID, a.CustomerID, a.AddressTypeID, a,IsPrimary,
                     d.CountryID, d.StateID, d.CountyID, d.DistrictID,
                     d.StreetID, d.StreetNumber, d.PostalCode,
                     d.AdditionalInformation, d.AddressImageID,
                     d.CreatedOn, d.CreatedBy
              FROM 
                   [sCustomerManagement].[tCustomerAddresses] a
              INNER JOIN 
                   [sCustomerManagement].[tAddresses] d ON a.AddressID = d.AddressID
              WHERE 
                    a.CustomerID = @CustomerID
             ) AS m
         FOR JSON AUTO)