SQL Server 2017如何实现符合特定规则的扁平化JSON输出

在SQL Server 2017中，现有如下数据表结构及测试数据：

create table Items (
    itemID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    name varchar(200)
)
create table Orders (
    orderID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    purchaseDate datetime,
    purchasedBy varchar(max)
)
create table Purchases (
    purchaseID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    orderID int FOREIGN KEY REFERENCES Orders(orderID),
    itemID int FOREIGN KEY REFERENCES Items(itemID),
    qty int,
    amount int
)

insert into Items values ('Apples'), ('Oranges'), ('Bananas');
insert into Orders values 
(dateadd(day, -5, getdate()), 'Person1'),
(dateadd(day, -4, getdate()), 'Person2'),
(dateadd(day, -3, getdate()), 'Person3')
;           
insert into Purchases values
 (1, 1, 1, 100),
 (1, 2, 2, 150),
 (1, 3, 5, 200),
 (2, 1, 2, 200),
 (2, 3, 1, 40),
 (3, 2, 1, 75)
;

需求 #

• 输出无嵌套的JSON键值对
• 未购买的商品对应的字段用null填充
• 将商品名称作为JSON键名的一部分（如apples_qty、apples_amount）

当前问题 #

现有查询返回嵌套结构的JSON，不符合预期：

select 
    o.orderID as 'orderID',
    max(o.purchaseDate) as 'purchaseDate',
    max(o.purchasedBy) as 'purchasedBy',
    (
        select 
            i.name as 'name',
            p.qty as 'qty',
            p.amount as 'amount'
        from
            Purchases p
            inner join Items i on p.itemID = i.itemID
        where o.orderID = p.orderID
        for json path
    )t
 from
    Orders o
 group by o.orderID
 for json path

输出的嵌套JSON：

[
  {
    "orderID": 1,
    "purchaseDate": "2023-11-05T20:32:08.870",
    "purchasedBy": "Person1",
    "t": [
      { "name": "Apples", "qty": 1, "amount": 100 },
      { "name": "Oranges", "qty": 2, "amount": 150 },
      { "name": "Bananas", "qty": 5, "amount": 200 }
    ]
  },
  {
    "orderID": 2,
    "purchaseDate": "2023-11-06T20:32:08.870",
    "purchasedBy": "Person2",
    "t": [
      { "name": "Apples", "qty": 2, "amount": 200 },
      { "name": "Bananas", "qty": 1, "amount": 40 }
    ]
  }
]

期望输出 #

扁平化且包含缺失值的JSON：

[
    {
      "orderID": 1,
      "purchaseDate": "2023-11-05T20:32:08.870",
      "purchasedBy": "Person1",
      "apples_qty": 1,
      "apples_amount": 100,
      "oranges_qty": 2,
      "oranges_amount": 150,
      "bananas_qty": 5,
      "bananas_amount": 200
    },
    {
      "orderID": 2,
      "purchaseDate": "2023-11-06T20:32:08.870",
      "purchasedBy": "Person2",
      "apples_qty": 2,
      "apples_amount": 200,
      "oranges_qty": null,
      "oranges_amount": null,
      "bananas_qty": 1,
      "bananas_amount": 40
    },
    {
      "orderID": 3,
      "purchaseDate": "2023-11-07T20:32:08.870",
      "purchasedBy": "Person3",
      "apples_qty": null,
      "apples_amount": null,
      "oranges_qty": 1,
      "oranges_amount": 75,
      "bananas_qty": null,
      "bananas_amount": null
    }
  ]

解决方案 #

静态方案（商品列表固定） #

如果商品名称不会变动，直接用条件聚合生成所需字段：

SELECT
    o.orderID,
    o.purchaseDate,
    o.purchasedBy,
    MAX(CASE WHEN i.name = 'Apples' THEN p.qty END) AS apples_qty,
    MAX(CASE WHEN i.name = 'Apples' THEN p.amount END) AS apples_amount,
    MAX(CASE WHEN i.name = 'Oranges' THEN p.qty END) AS oranges_qty,
    MAX(CASE WHEN i.name = 'Oranges' THEN p.amount END) AS oranges_amount,
    MAX(CASE WHEN i.name = 'Bananas' THEN p.qty END) AS bananas_qty,
    MAX(CASE WHEN i.name = 'Bananas' THEN p.amount END) AS bananas_amount
FROM Orders o
LEFT JOIN Purchases p ON o.orderID = p.orderID
LEFT JOIN Items i ON p.itemID = i.itemID
GROUP BY o.orderID, o.purchaseDate, o.purchasedBy
FOR JSON PATH, INCLUDE_NULL_VALUES;

动态方案（商品列表可扩展） #

如果Items表会新增商品，用动态SQL自动生成对应字段，无需修改查询：

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成所有商品对应的qty和amount列定义
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN i.name = ''', REPLACE(name, '''', ''''''), ''' THEN p.qty END) AS ', QUOTENAME(LOWER(name) + '_qty'),
        ', MAX(CASE WHEN i.name = ''', REPLACE(name, '''', ''''''), ''' THEN p.amount END) AS ', QUOTENAME(LOWER(name) + '_amount')
    ), ', '
)
FROM Items;

-- 构建完整查询语句
SET @sql = CONCAT('
SELECT
    o.orderID,
    o.purchaseDate,
    o.purchasedBy,
    ', @cols, '
FROM Orders o
LEFT JOIN Purchases p ON o.orderID = p.orderID
LEFT JOIN Items i ON p.itemID = i.itemID
GROUP BY o.orderID, o.purchaseDate, o.purchasedBy
FOR JSON PATH, INCLUDE_NULL_VALUES;
');

-- 执行动态SQL
EXEC sp_executesql @sql;

方案说明 #

• 使用LEFT JOIN确保所有订单都被包含，即使没有购买任何商品
• 通过CASE WHEN+MAX聚合，为每个商品生成对应的qty和amount字段，未购买的商品返回null
• INCLUDE_NULL_VALUES参数保证JSON中保留null值的字段，符合需求
• 动态方案利用STRING_AGG自动生成列定义，适配商品列表的变化

内容的提问来源于stack exchange，提问作者boosted-coder