SQL Server/C#中按指定字段分组后合并生成JSON记录的方法

SQL Server 2016及以上版本、.NET生态下的C#都提供开箱即用的原生能力，无需额外第三方依赖即可完成需求，具体实现方式如下：

SQL Server 实现 #

SQL Server从2016版本开始内置JSON处理能力，可以直接通过内置语法生成目标结构，无需手动拼接字符串处理转义。

通用写法（兼容SQL Server 2016-2022所有支持JSON的版本） #

-- 测试源数据（实际使用时替换为你的业务表即可）
WITH SourceData AS (
    SELECT ORG = 'A', Tenant = 'T1', Dimension = 'MAU', Count = 100
    UNION ALL SELECT 'A', 'T1', 'WAU', 70
    UNION ALL SELECT 'B', 'T2', 'MAU', 50
    UNION ALL SELECT 'B', 'T2', 'Retention', 30
)
-- 核心查询逻辑
SELECT 
    s1.ORG,
    s1.Tenant,
    CustomData = (
        SELECT 
            JSON_QUERY(CONCAT('{"', STRING_ESCAPE(s2.Dimension, 'json'), '":', s2.Count, '}'))
        FROM SourceData s2
        WHERE s2.ORG = s1.ORG AND s2.Tenant = s1.Tenant
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )
FROM SourceData s1
GROUP BY s1.ORG, s1.Tenant

简化写法（仅支持SQL Server 2022及以上版本） #

2022版本新增了JSON_OBJECT_AGG聚合函数，专门用于分组生成JSON对象，写法更简洁：

WITH SourceData AS (
    SELECT ORG = 'A', Tenant = 'T1', Dimension = 'MAU', Count = 100
    UNION ALL SELECT 'A', 'T1', 'WAU', 70
    UNION ALL SELECT 'B', 'T2', 'MAU', 50
    UNION ALL SELECT 'B', 'T2', 'Retention', 30
)
SELECT 
    ORG,
    Tenant,
    CustomData = JSON_OBJECT_AGG(Dimension, Count)
FROM SourceData
GROUP BY ORG, Tenant

上述两种写法执行后返回结果完全匹配需求：

注意：你给出的示例中CustomData值的键名没有双引号，属于非标准JSON格式，上述SQL生成的是符合RFC 8259规范的标准JSON，所有JSON解析组件都可以正常读取，不建议生成非标准格式，避免特殊字符导致的解析异常。

C# 实现 #

如果是在应用层处理数据，C#可以通过内置LINQ分组能力+JSON序列化组件实现，无需复杂逻辑：

using System.Collections.Generic;
using System.Linq;
using System.Text.Json;

// 原始数据实体类，对应你的表结构
public class MetricRecord
{
    public string ORG { get; set; }
    public string Tenant { get; set; }
    public string Dimension { get; set; }
    public int Count { get; set; }
}

// 模拟从数据库查询得到的原始数据
List<MetricRecord> sourceData = new List<MetricRecord>
{
    new() { ORG = "A", Tenant = "T1", Dimension = "MAU", Count = 100 },
    new() { ORG = "A", Tenant = "T1", Dimension = "WAU", Count = 70 },
    new() { ORG = "B", Tenant = "T2", Dimension = "MAU", Count = 50 },
    new() { ORG = "B", Tenant = "T2", Dimension = "Retention", Count = 30 }
};

// 分组转换为目标结构
var result = sourceData
    .GroupBy(x => new { x.ORG, x.Tenant })
    .Select(g => new 
    {
        g.Key.ORG,
        g.Key.Tenant,
        // 同组维度转为字典，序列化后就是要求的键值对JSON对象
        CustomData = g.ToDictionary(x => x.Dimension, x => x.Count)
    })
    .ToList();

// 需要输出JSON字符串时，直接调用内置序列化方法即可（.NET Core 3.0+ 内置System.Text.Json）
string jsonOutput = JsonSerializer.Serialize(result, new JsonSerializerOptions { WriteIndented = true });

如果使用.NET Framework，只需要引入官方维护的Newtonsoft.Json包，序列化逻辑几乎一致，不需要改动核心分组代码。

原生支持情况说明 #

• SQL Server 2016及以上版本原生提供完整的JSON生成、查询能力，2022版本新增的聚合函数进一步简化了这类分组生成JSON的场景，完全开箱即用；2016以下版本没有原生JSON支持，需要手动拼接字符串，容易出现转义问题，不推荐使用。
• C#在.NET Core 3.0及以上版本中内置了System.Text.Json组件，配合LINQ原生的分组、字典转换能力，可以零额外依赖实现需求；低版本.NET Framework通过官方JSON库也可以快速实现。

内容的提问来源于stack exchange，提问作者Nikesh