将GA API输出转换为结构化json格式

Question

我想使用Jolt Transformation将json数据转换为结构化json格式。

输入数据:

"containsSampledData": false,
"columnHeaders": [
{
"name": "ga:pagePath",
"columnType": "DIMENSION",
"dataType": "STRING"
},
{
"name": "ga:eventCategory",
"columnType": "DIMENSION",
"dataType": "STRING"
},
{
"name": "ga:eventAction",
"columnType": "DIMENSION",
"dataType": "STRING"
},
{
"name": "ga:totalEvents",
"columnType": "METRIC",
"dataType": "INTEGER"
},
{
"name": "ga:uniqueEvents",
"columnType": "METRIC",
"dataType": "INTEGER"
},
{
"name": "ga:avgEventValue",
"columnType": "METRIC",
"dataType": "FLOAT"
}
],
"totalsForAllResults": {
"ga:totalEvents": "174594",
"ga:uniqueEvents": "107567",
"ga:avgEventValue": "57472.307753989255"
},
"rows": [
[
"/",
"BannerPromotion",
"(not set)",
"9",
"6",
"0.0"
],
[
"/",
"BannerPromotion",
"Sample Test",
"30",
"25",
"0.0"
],
[
"/",
"BannerPromotion",
"Sample Test",
"3",
"3",
"0.0"
]

预期输出:

{
"pagePath" : "/",
"eventCategory" : "BannerPromotion",
"eventAction" : "(not set)",
"totalEvents" : "9",
"uniqueEvents" : "6",
"avgEventValue" : "0.0"
},
{
"pagePath" : "/",
"eventCategory" : "BannerPromotion",
"eventAction" : "Sample Test",
"totalEvents" : "3",
"uniqueEvents" : "3",
"avgEventValue" : "0.0"
}

场景是我从谷歌分析API中提取数据，它给了我上面的输入数据格式，这不是将数据存储到数据库表之前的正确格式。

提前谢谢你。

Answer 1

考虑这是您的输入

输入:

{
"containsSampledData": false,
"columnHeaders": [
{
"name": "ga:pagePath",
"columnType": "DIMENSION",
"dataType": "STRING"
},
{
"name": "ga:eventCategory",
"columnType": "DIMENSION",
"dataType": "STRING"
},
{
"name": "ga:eventAction",
"columnType": "DIMENSION",
"dataType": "STRING"
},
{
"name": "ga:totalEvents",
"columnType": "METRIC",
"dataType": "INTEGER"
},
{
"name": "ga:uniqueEvents",
"columnType": "METRIC",
"dataType": "INTEGER"
},
{
"name": "ga:avgEventValue",
"columnType": "METRIC",
"dataType": "FLOAT"
}
],
"totalsForAllResults": {
"ga:totalEvents": "174594",
"ga:uniqueEvents": "107567",
"ga:avgEventValue": "57472.307753989255"
},
"rows": [
[
"/",
"BannerPromotion",
"(not set)",
"9",
"6",
"0.0"
],
[
"/",
"BannerPromotion",
"Sample Test",
"30",
"25",
"0.0"
],
[
"/",
"BannerPromotion",
"Sample Test",
"3",
"3",
"0.0"
]
]
}

您可以使用此Jolt以所需的格式创建输出

震动规范:

[
{
"operation": "shift",
"spec": {
"rows": {
"*": {
"0": "[&1].pagePath",
"1": "[&1].eventCategory",
"2": "[&1].eventAction",
"3": "[&1].totalEvents",
"4": "[&1].uniqueEvents",
"5": "[&1].avgEventValue"
}
}
}
}
]