Problem with using of FOR JSON AUTO in SQL Server-CodePudding

I am using FOR JSON AUTO in SQL server database, to convert my query's result to the JSON format. in my query, I joined order table to two other tables.

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

Result:

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

But it's not correct, because in this scenario customer and store are sibling and they have same parent, and both of them joined with the order table directly, correct JSON must be such as this:

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

how can I do that? Are there any option for this in SQL? (I don't want to use inner select.)

CodePudding user response：

If there are one-to-one relationships between Orders and Customer and between Orders and Store then you can make the desired output by using PATH option and dot-separated column names:

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

But if there are one-to-many relationships then you have to use nested queries:

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