Problem with using of FOR JSON AUTO in SQL Server

Question

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.)

Please add what database this is.

kometen
– kometen

2021-12-12 07:48:47 +00:00
Commented Dec 12, 2021 at 7:48 — kometen
– kometen, Commented Dec 12, 2021 at 7:48

Charlieface · Accepted Answer · 2021-12-12 16:50:19Z

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

Collectives™ on Stack Overflow

Problem with using of FOR JSON AUTO in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related