How can I do a "FOR JSON" with 2 joins

Question

I can't get this to work properly. I have 4 tables: Products, Suppliers, X_Product_Suppliers and Comments. I want to query them all and put them into JSON using the following query:

WITH Products (Id, Name, Price) As (
    SELECT 1, 'First Product', 10
), Suppliers (Id, Name) As (
    SELECT 1, 'Factory1' UNION ALL
    SELECT 2, 'Factory2'
), Comments (Id, [Text], ProductId) As (
    SELECT 1, 'Good Product', 1 UNION ALL
    SELECT 2, 'Fantastic!'  , 1
), X_Product_Supplier (ProductId, SupplierId) As (
    SELECT 1, 1 UNION ALL
    SELECT 1, 2
)
SELECT Products.*, Suppliers.*, Comments.* FROM Products
LEFT OUTER JOIN X_Product_Supplier ON X_Product_Supplier.ProductId = Products.Id
LEFT OUTER JOIN Suppliers ON X_Product_Supplier.SupplierId = Suppliers.Id
LEFT OUTER JOIN Comments ON Comments.ProductId = Products.Id
FOR JSON AUTO

For some reason sql-server will nest the comments under the supplier instead of under the product:

   {  
      "Id":1,
      "Name":"First Product",
      "Price":"10",
      "Suppliers":[  
         {  
            "Id":1,
            "Name":"Factory1",
            "Comments":[  //THIS SHOULD BE UNDER PRODUCT, NOT SUPPLIER
               {  
                  "Id":1,
                  "Text":"Good Product",
                  "ProductId":1
               },
               {  
                  "Id":2,
                  "Text":"Fantastic!",
                  "ProductId":1
               }
            ]
         },
         {  
            "Id":2,
            "Name":"Factory2",
            "Comments":[  //THIS IS NOW DUPLICATE
               {  
                  "Id":1,
                  "Text":"Good Product",
                  "ProductId":1
               },
               {  
                  "Id":2,
                  "Text":"Fantastic!",
                  "ProductId":1
               }
            ]
         }
      ]
   }

What I actually want is this:

   {  
      "Id":1,
      "Name":"First Product",
      "Price":"10",
      "Suppliers":[  
         {  
            "Id":1,
            "Name":"Factory1"
         },
         {  
            "Id":2,
            "Name":"Factory2"
         }
      ],
      "Comments":[  
               {  
                  "Id":1,
                  "Text":"Good Product",
                  "ProductId":1
               },
               {  
                  "Id":2,
                  "Text":"Fantastic!",
                  "ProductId":1
               }
            ]
   }

How do I do this?

Answer 1

If anyone else is looking for answer here is simple query I have written. Change the query as per your schema and it should give your proper structured result.

SELECT Products.*,
(SELECT Suppliers.*
 FROM Suppliers
 WHERE Suppliers.Id = Products.SuppliersId
 FOR JSON AUTO) As Suppliers,
(SELECT Comments.*
 FROM Comments 
 WHERE Comments.ProductId = Products.Id
 FOR JSON AUTO) As Comments
FROM Products
FOR JSON AUTO

Answer 2

I know its late, but i faced with same problem but the answers could not help in filtering the null values in where clause, my solution below

SELECT  Products.*, 
        Suppliers.Id as [Suppliers.Id], 
        Suppliers.Name as [Suppliers.Name],
        Comments.Id as [Comments.Id],
        Comments.Text as [Comments.Text],
        Comments.ProductId as [Comments.ProductId] 
FROM Products
LEFT OUTER JOIN X_Product_Supplier ON X_Product_Supplier.ProductId = Products.Id
LEFT OUTER JOIN Suppliers ON X_Product_Supplier.SupplierId = Suppliers.Id
LEFT OUTER JOIN Comments ON Comments.ProductId = Products.Id
FOR JSON PATH 

Tested and working on SQL SERVER 2017