基于System.Text.Json为GA4 API数据添加filteredTotals聚合行

• 项目使用System.Text.Json处理GA4 API返回的JSON数据
• 需要为原始数据添加filteredTotals字段，该字段包含所有rows中对应metricValues的数值总和，维度值固定为FILTERED_TOTAL
• 原始JSON不含filteredTotals，需自行计算生成

原始GA4 API返回JSON：

{"dimensionHeaders":[{"name":"sessionSourceMedium"}],"metricHeaders":[{"name":"newUsers","type":"TYPE_INTEGER"},{"name":"activeUsers","type":"TYPE_INTEGER"},{"name":"sessions","type":"TYPE_INTEGER"}],"rows":[{"dimensionValues":[{"value":"google / organic"}],"metricValues":[{"value":"1441844"},{"value":"1527239"},{"value":"2614845"}]},{"dimensionValues":[{"value":"(direct) / (none)"}],"metricValues":[{"value":"676615"},{"value":"680490"},{"value":"865201"}]},{"dimensionValues":[{"value":"google / cpc"}],"metricValues":[{"value":"239104"},{"value":"248233"},{"value":"304656"}]},{"dimensionValues":[{"value":"bing / organic"}],"metricValues":[{"value":"114356"},{"value":"127718"},{"value":"211394"}]},{"dimensionValues":[{"value":"yahoo / organic"}],"metricValues":[{"value":"17582"},{"value":"18963"},{"value":"30481"}]},{"dimensionValues":[{"value":"linkedin.com / referral"}],"metricValues":[{"value":"21882"},{"value":"23792"},{"value":"28765"}]},{"dimensionValues":[{"value":"bing / cpc"}],"metricValues":[{"value":"20550"},{"value":"22868"},{"value":"26812"}]},{"dimensionValues":[{"value":"duckduckgo / organic"}],"metricValues":[{"value":"14561"},{"value":"15105"},{"value":"24291"}]}]}

1. 定义匹配的数据模型 #

创建对应GA4 JSON结构的类，用于序列化/反序列化操作：

public class Ga4ReportResponse
{
    public List<DimensionHeader> DimensionHeaders { get; set; }
    public List<MetricHeader> MetricHeaders { get; set; }
    public List<Row> Rows { get; set; }
    public List<Row> FilteredTotals { get; set; } // 新增的总计字段
}

public class DimensionHeader
{
    public string Name { get; set; }
}

public class MetricHeader
{
    public string Name { get; set; }
    public string Type { get; set; }
}

public class Row
{
    public List<DimensionValue> DimensionValues { get; set; }
    public List<MetricValue> MetricValues { get; set; }
}

public class DimensionValue
{
    public string Value { get; set; }
}

public class MetricValue
{
    public string Value { get; set; }
}

2. 反序列化并计算总计 #

读取原始JSON后，遍历所有行计算每个指标的总和，生成总计行：

// 原始JSON字符串（替换为你的GA4 API返回内容）
string rawJson = "你的GA4 JSON数据";

// 反序列化为对象，忽略大小写匹配字段
var options = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
var report = JsonSerializer.Deserialize<Ga4ReportResponse>(rawJson, options);

// 初始化总和数组，长度与指标数量一致
int metricCount = report.MetricHeaders.Count;
long[] totals = new long[metricCount];

// 遍历所有行累加指标值
foreach (var row in report.Rows)
{
    for (int i = 0; i < metricCount; i++)
    {
        if (long.TryParse(row.MetricValues[i].Value, out long value))
        {
            totals[i] += value;
        }
    }
}

// 构建总计行
var totalRow = new Row
{
    DimensionValues = new List<DimensionValue> { new DimensionValue { Value = "FILTERED_TOTAL" } },
    MetricValues = totals.Select(t => new MetricValue { Value = t.ToString() }).ToList()
};

// 添加到filteredTotals字段
report.FilteredTotals = new List<Row> { totalRow };

3. 序列化回JSON #

将处理后的对象重新序列化为带总计的JSON：

string updatedJson = JsonSerializer.Serialize(report, new JsonSerializerOptions { WriteIndented = true });
// 输出或使用处理后的JSON
Console.WriteLine(updatedJson);

关键说明 #

• 使用long类型计算总和，避免大数值场景下的整数溢出
• 启用PropertyNameCaseInsensitive确保字段匹配不受大小写影响
• 最终生成的JSON会包含符合要求的filteredTotals字段

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