SQL 内连接与 For JSON 层次结构

Question

我正在尝试使用内部联接来联接两个 SQL 表，然后从我的过程中将它们作为 JSON 返回。

我的选择语句是:

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)

结果是这样的:

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

问题是我不希望数组 AddressDetails 中的信息被嵌套。是否有可能那里的信息在外面，所以我可以接收 2 个平面对象，而不需要嵌套信息？

谢谢

Answer 1

考虑使用带有点语法的 PATH 模式，并将所有字段映射到地址，如 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)

或者，使用派生表:

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)