在SQLServer中使用FORJSONAUTO的問題-有解無憂

我FOR JSON AUTO在 SQL 服務器資料庫中使用，將我的查詢結果轉換為 JSON 格式。在我的查詢中，我將訂單表加入了另外兩個表。

SELECT
    orders.[Code], orders.[Total], orders.[Discount], 
    customer.[Name], customer.[PhoneNumber], 
    store.[Name], store.[Address]
FROM
    Orders orders 
INNER JOIN
    Customers customer ON (orders.[CustomerID] = customer.[ID]) 
INNER JOIN
    Stores store ON (orders.[StoreID] = store.[ID])
FOR JSON AUTO

結果：

[
  {
    "Code": "1528",
    "Total": 5000,
    "Discount": 20,
    "customer": [
      {
        "Name": "Alex",
        "PhoneNumber": "(548) 123-5555",
        "store": [
          {
            "Name": "Apple",
            "Address": "E. Santa rd"
          }
        ]
      }
    ]
  },
  {
    "Code": "1687",
    "Total": 3000,
    "Discount": 10,
    "customer": [
      {
        "Name": "John",
        "PhoneNumber": "(226) 354-7896",
        "store": [
          {
            "Name": "Sony",
            "Address": "W. Atlantic ave"
          }
        ]
      }
    ]
  }
]

但這是不正確的，因為在這種情況下，客戶和商店是兄弟姐妹，他們有相同的父級，并且都直接與訂單表相連，正確的 JSON 必須是這樣的：

[
    {
        "Code": "1528",
        "Total": 5000,
        "Discount": 20,
        "customer": [
            {
                "Name": "Alex",
                "PhoneNumber": "(548) 123-5555"
            }
        ],
        "store": [
            {
                "Name": "Apple",
                "Address": "E. Santa rd"
            }
        ]
    },
    {
        "Code": "1687",
        "Total": 3000,
        "Discount": 10,
        "customer": [
            {
                "Name": "John",
                "PhoneNumber": "(226) 354-7896"
            }
        ],
        "store": [
            {
                "Name": "Sony",
                "Address": "W. Atlantic ave"
            }
        ]
    }
]

我怎樣才能做到這一點？在 SQL 中有任何選項嗎？（我不想使用內部選擇。）

uj5u.com熱心網友回復：

如果Ordersand 和Customerand 之間存在一對一的關系Orders，Store那么您可以使用PATH選項和點分隔的列名來生成所需的輸出：

SELECT
    orders.[Code], orders.[Total], orders.[Discount], 
    customer.[Name] AS [Customer.Name], customer.[PhoneNumber] AS [Customer.PhoneNumber], 
    store.[Name] AS [Store.Name], store.[Address] AS [Store.Address]
FROM
    Orders orders 
INNER JOIN
    Customers customer ON (orders.[CustomerID] = customer.[ID]) 
INNER JOIN
    Stores store ON (orders.[StoreID] = store.[ID])
FOR JSON PATH

但如果存在一對多關系，則必須使用嵌套查詢：

SELECT
    orders.[Code], orders.[Total], orders.[Discount], 
    (SELECT [Name], [PhoneNumber] FROM Customers WHERE Customers.ID=Orders.CustomerID FOR JSON AUTO) AS Customers,
    (SELECT [Name], [Address] FROM Stores WHERE Stores.ID=Orders.StoreID FOR JSON AUTO) AS Stores
FROM
    Orders orders 
FOR JSON AUTO

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/387993.html

標籤：sql json sql-server 加入

上一篇：SQLServer：連接2個表產生所需的輸出

下一篇：從給定陣列回傳多維陣列