如何将Google Analytics API返回JSON按Headers与Values匹配转换结构？

将Google Analytics API返回的包含dimensionHeaders、metricHeaders和rows的JSON结构，转换为以Headers的name为键、对应Values的value为值的扁平对象数组，可通过以下JOLT规范实现：

输入数据 #

{
  "dimensionHeaders": [
    {
      "name": "date"
    },
    {
      "name": "sessionMedium"
    },
    {
      "name": "sessionCampaignName"
    }
  ],
  "metricHeaders": [
    {
      "name": "sessions"
    },
    {
      "name": "bounceRate"
    },
    {
      "name": "newUsers"
    }
  ],
  "rows": [
    {
      "dimensionValues": [
        {
          "value": "20230526"
        },
        {
          "value": "organic"
        },
        {
          "value": "RV_olov_23"
        }
      ],
      "metricValues": [
        {
          "value": "47"
        },
        {
          "value": "1"
        },
        {
          "value": "40"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "20230527"
        },
        {
          "value": "online_plus"
        },
        {
          "value": "Borhoni_aqua_23_may"
        }
      ],
      "metricValues": [
        {
          "value": "31"
        },
        {
          "value": "0.25"
        },
        {
          "value": "10"
        }
      ]
    }
  ],
  "rowCount": 2
}

JOLT转换规范 #

[
  {
    "operation": "shift",
    "spec": {
      "rows": {
        "*": {
          "@(2,dimensionHeaders[&1].name)": "[&1].@(dimensionValues[&1].value)",
          "@(2,metricHeaders[&1].name)": "[&1].@(metricValues[&1].value)"
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "sessions": "=toInteger",
        "bounceRate": "=toDouble",
        "newUsers": "=toInteger"
      }
    }
  }
]

转换结果 #

[
  {
    "date": "20230526",
    "sessionMedium": "organic",
    "sessionCampaignName": "RV_olov_23",
    "sessions": 47,
    "bounceRate": 1,
    "newUsers": 40
  },
  {
    "date": "20230527",
    "sessionMedium": "online_plus",
    "sessionCampaignName": "Borhoni_aqua_23_may",
    "sessions": 31,
    "bounceRate": 0.25,
    "newUsers": 10
  }
]

转换逻辑说明 #

1. Shift操作：通过层级索引匹配Headers与Values的对应关系。@(2,dimensionHeaders[&1].name)表示向上两级获取dimensionHeaders数组中对应索引的name作为键，@(dimensionValues[&1].value)取当前行对应索引的value作为值；Metrics部分同理完成匹配。
2. Modify操作：将字符串类型的数值字段转换为对应数字类型，贴合目标格式要求。

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