Match values from different arrays

Question

I receive JSON from Google Analytics API. It looks like this:

{
  "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
}

I need to perform complex manipulations. Arrays ending with ...Headers should match with arrays ending with ....Values. So, what should we exactly compare:

• Values from array dimensionHeaders should match with values from array dimensionValues (inside rows array);
• Values from metricHeaders should match with values from array metricValues.

And finally, it means that values from ...Headers arrays - are keys names and values from ...Values arrays - are values for these new keys.

So let's see what we should get:

[
  {
    "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
  }
]

I don't know exactly if is it possible to do with JOLT or not. I will be grateful for any help!

Answer 1

You can use this spec:

[
  {
    "operation": "shift",
    "spec": {
      "rows": {
        "*": {
          "dimensionValues": {
            "*": {
              "value": "[&3].@(5,dimensionHeaders[&1].name)"
            }
          },
          "metricValues": {
            "*": {
              "value": "[&3].@(5,metricHeaders[&1].name)"
            }
          }
        }
      }
    }
  }
]

If you need to change your string numbers to integer type you can add the following spec to the above spec:

{
  "operation": "modify-overwrite-beta",
  "spec": {
    "*": {
      "sessions": "=toInteger",
      "bounceRate": "=toDouble",
      "newUsers": "=toInteger"
    }
  }
}