CODEWITHSUNDEEP
sql-serverjson
Yolofy
Yolofy

Reputation: 521

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

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?

Upvotes: 6

Views: 5179

Answers (3)

Anuroop S
Anuroop S

Reputation: 111

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

Upvotes: 1

Muheeb
Muheeb

Reputation: 226

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

Upvotes: 16

Y.B.
Y.B.

Reputation: 3586

SQL Server will convert result table into JSON with columns going in the same order as they appear in SELECT statement. It would not make any assumptions of column grouping. To help it Suppliers and Comments can be pre-arranged into JSON objects for each Product:

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.*,
    (SELECT Suppliers.*
     FROM Suppliers
     INNER JOIN X_Product_Supplier ON X_Product_Supplier.SupplierId = Suppliers.Id
     WHERE X_Product_Supplier.ProductId = Products.Id
     FOR JSON AUTO, TYPE) As Suppliers,
    (SELECT Comments.*
     FROM Comments
     WHERE Comments.ProductId = Products.Id
     FOR JSON AUTO, TYPE) As Comments
FROM Products
FOR JSON AUTO
  • Tested on XML as I do not have access to SQL Server 2016 at the moment. Please let me know if this does not work.

Upvotes: 1

Related Questions