首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何使用2个连接执行"FOR JSON“

如何使用2个连接执行"FOR JSON“
EN

Stack Overflow用户
提问于 2017-03-29 21:03:15
回答 2查看 3.4K关注 0票数 6

我不能让它正常工作。我有4个表:产品,供应商,X_Product_Suppliers和评论。我想要查询它们,并使用以下查询将它们放到JSON中:

代码语言:javascript
复制
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

由于某种原因,sql-server会将注释嵌套在供应商下,而不是产品下:

代码语言:javascript
复制
   {  
      "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
               }
            ]
         }
      ]
   }

我真正想要的是:

代码语言:javascript
复制
   {  
      "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
               }
            ]
   }

我该怎么做呢?

EN

回答 2

Stack Overflow用户

发布于 2018-07-26 08:02:13

如果其他人正在寻找答案,这里是我写的简单的查询。根据您的模式更改查询,它应该会给出适当的结构化结果。

代码语言:javascript
复制
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
票数 15
EN

Stack Overflow用户

发布于 2019-06-17 01:19:01

我知道已经很晚了,但是我遇到了同样的问题,但是答案不能帮助过滤where子句中的空值,我的解决方案如下

代码语言:javascript
复制
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 

在SQL SERVER 2017上测试和工作

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/43094351

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档