使用FOR JSON将平面SQL行转换为嵌套的JSON数组

Question

因此，我有一个简单的视图，如下所示：

Name    | Type        | Product     | QuantitySold
------------------------------------------------------
Walmart | Big Store   | Gummy Bears | 10
Walmart | Big Store   | Toothbrush  | 6
Target  | Small Store | Toothbrush  | 2

不使用嵌套查询，使用sql的FOR JSON子句，就可以很容易地将其转换为JSON。

[
{
"Type": "Big Store",
"Stores": [
{
"Name": "Walmart",
"Products": [
{
"Name": "Gummy Bears",
"QuantitySold": 10
},
{
"Name": "Toothbrush",
"QuantitySold": 6
}
]
}
]
},
{
"Type": "Smaller Store",
"Stores": [
{
"Name": "Target",
"Products": [
{
"Name": "Toothbrush",
"QuantitySold": 2
}
]
}
]
}
]

基本上按类型分组，然后存储，行项目。我的尝试远低于此。不确定如何正确地对行进行分组。

SELECT Type, (
SELECT Store,
(SELECT Product,QuantitySold from MyTable m3 where m3.id=m2.id for json path) as Products
FROM MyTable m2 where m1.ID = m2.ID for json path) as Stores
) as Types FROM MyTable m1

Answer 1

您可以尝试以下操作：

DECLARE @Data TABLE (
Name VARCHAR(20), Type VARCHAR(20), Product VARCHAR(20), QuantitySold INT
);
INSERT INTO @Data ( Name, Type, Product, QuantitySold ) VALUES
( 'Walmart', 'Big Store', 'Gummy Bears', 10 ),
( 'Walmart', 'Big Store', 'Toothbrush', 6 ),
( 'Target', 'Small Store', 'Toothbrush', 2 );
SELECT DISTINCT
t.[Type],
Stores
FROM @Data AS t
OUTER APPLY (

SELECT (
SELECT DISTINCT [Name], Products FROM @Data x
OUTER APPLY (
SELECT (
SELECT Product AS [Name], QuantitySold FROM @Data n WHERE n.[Name] = x.[Name]
FOR JSON PATH
) AS Products
) AS p
WHERE x.[Type] = t.[Type]
FOR JSON PATH
) AS Stores
) AS Stores
ORDER BY [Type]
FOR JSON PATH;

返回

[{
"Type": "Big Store",
"Stores": [{
"Name": "Walmart",
"Products": [{
"Name": "Gummy Bears",
"QuantitySold": 10
}, {
"Name": "Toothbrush",
"QuantitySold": 6
}]
}]
}, {
"Type": "Small Store",
"Stores": [{
"Name": "Target",
"Products": [{
"Name": "Toothbrush",
"QuantitySold": 2
}]
}]
}]

Answer 2

如果您已经规范化了数据结构，那么您可以使用另一种方法。

--Let's assume that Types are stored like this
DECLARE @Types TABLE (
id int,
Type nvarchar(20)
);
INSERT INTO @Types VALUES (1, N'Big Store'), (2, N'Small Store');
--Stores in separate table
DECLARE @Stores TABLE (
id int,
Name nvarchar(10),
TypeId int
);
INSERT INTO @Stores VALUES (1, N'Walmart', 1), (2, N'Target', 2), 
(3, N'Tesco', 2); -- I added one more just for fun
--Products table
DECLARE @Products TABLE (
id int,
Name nvarchar(20)
);
INSERT INTO @Products VALUES (1, N'Gummy Bears'), (2, N'Toothbrush'), 
(3, N'Milk'), (4, N'Ball') -- Added some here
-- And here comes the sales
DECLARE @Sales TABLE (
StoreId int,
ProductId int,
QuantitySold int
);
INSERT INTO @Sales VALUES (1, 1, 10), (1, 2, 6), (2, 2, 2), 
(3, 4, 15), (3, 3, 7); -- I added few more

现在我们可以加入表格，得到您需要的结果

SELECT Type = Type.Type,
Name = [Stores].Name,
Name = Products.Product,
QuantitySold = Products.QuantitySold
FROM (
SELECT s.StoreId,
p.Name Product,
s.QuantitySold
FROM @Sales s
INNER JOIN @Products p 
ON p.id = s.ProductId
) Products
INNER JOIN @Stores Stores
ON Stores.Id = Products.StoreId
INNER JOIN @Types [Type]
ON Stores.TypeId = [Type].id
ORDER BY Type.Type, [Stores].Name
FOR JSON AUTO;

输出：

[
{
"Type": "Big Store",
"Stores": [
{
"Name": "Walmart",
"Products": [
{
"Name": "Gummy Bears",
"QuantitySold": 10
},
{
"Name": "Toothbrush",
"QuantitySold": 6
}
]
}
]
},
{
"Type": "Small Store",
"Stores": [
{
"Name": "Target",
"Products": [
{
"Name": "Toothbrush",
"QuantitySold": 2
}
]
},
{
"Name": "Tesco",
"Products": [
{
"Name": "Ball",
"QuantitySold": 15
},
{
"Name": "Milk",
"QuantitySold": 7
}
]
}
]
}
]