Search code examples
jsonjolt

Join values from array as keys to values from another array (JOLT)

From this JSON below:

{
   "query":{
      "ids":[
         95445875
      ],
      "group": "day",
      "dimensions":[
         "ym:s:date",
         "ym:s:lastSignUTMSource",
         "ym:s:lastSignUTMMedium",
         "ym:s:lastSignUTMCampaign",
         "ym:s:lastSignUTMContent",
         "ym:s:lastSignUTMTerm"
      ],
      "metrics":[
         "ym:s:goal315094773visits",
         "ym:s:goal318656867visits"
      ]
   },
   "data":[
      {
         "dimensions":[
            {
               "name":"2024-01-12"
            },
            {
               "name":"5ka"
            },
            {
               "name":"SITE"
            },
            {
               "name":"5ka_HR_nov23"
            },
            {
               "name":"button"
            },
            {
               "name":"click"
            }
         ],
         "metrics":[
            64.0,
            2.0
         ]
      },
      {
         "dimensions":[
            {
               "name":"2024-01-13"
            },
            {
               "name":"5ka"
            },
            {
               "name":"SITE"
            },
            {
               "name":"5ka_HR_nov23"
            },
            {
               "name":"button"
            },
            {
               "name":"click"
            }
         ],
         "metrics":[
            13.0,
            0.0
         ]
      },
      {
         "dimensions":[
            {
               "name":"2024-01-13"
            },
            {
               "name":"Yandex"
            },
            {
               "name":"SEM"
            },
            {
               "name":"5ka_HR_oct-dec'23"
            },
            {
               "name":"TGB"
            },
            {
               "name":"shop"
            }
         ],
         "metrics":[
            5.0,
            4.0
         ]
      },
      {
         "dimensions":[
            {
               "name":"2024-01-12"
            },
            {
               "name":"Yandex"
            },
            {
               "name":"SEM"
            },
            {
               "name":"5ka_HR_oct-dec'23"
            },
            {
               "name":"TGB"
            },
            {
               "name":"shop"
            }
         ],
         "metrics":[
            3.0,
            0.0
         ]
      }
   ],
   "total_rows":4
}

I expect to get this result:

[
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-12",
      "utm_source":"5ka",
      "utm_medium":"SITE",
      "utm_campaign":"5ka_HR_nov23",
      "utm_content":"button",
      "utm_term":"click",
      "goal_id":"315094773",
      "conversions":64.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-12",
      "utm_source":"5ka",
      "utm_medium":"SITE",
      "utm_campaign":"5ka_HR_nov23",
      "utm_content":"button",
      "utm_term":"click",
      "goal_id":"318656867",
      "conversions":2.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-13",
      "utm_source":"5ka",
      "utm_medium":"SITE",
      "utm_campaign":"5ka_HR_nov23",
      "utm_content":"button",
      "utm_term":"click",
      "goal_id":"315094773",
      "conversions":13.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-13",
      "utm_source":"5ka",
      "utm_medium":"SITE",
      "utm_campaign":"5ka_HR_nov23",
      "utm_content":"button",
      "utm_term":"click",
      "goal_id":"318656867",
      "conversions":0.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-12",
      "utm_source":"Yandex",
      "utm_medium":"SEM",
      "utm_campaign":"5ka_HR_oct-dec'23",
      "utm_content":"TGB",
      "utm_term":"shop",
      "goal_id":"315094773",
      "conversions":5.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-12",
      "utm_source":"Yandex",
      "utm_medium":"SEM",
      "utm_campaign":"5ka_HR_oct-dec'23",
      "utm_content":"TGB",
      "utm_term":"shop",
      "goal_id":"318656867",
      "conversions":4.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-13",
      "utm_source":"Yandex",
      "utm_medium":"SEM",
      "utm_campaign":"5ka_HR_oct-dec'23",
      "utm_content":"TGB",
      "utm_term":"shop",
      "goal_id":"315094773",
      "conversions":3.0
   },
   {
      "counter_id":95445875,
      "date_of_visit":"2024-01-13",
      "utm_source":"Yandex",
      "utm_medium":"SEM",
      "utm_campaign":"5ka_HR_oct-dec'23",
      "utm_content":"TGB",
      "utm_term":"shop",
      "goal_id":"318656867",
      "conversions":0.0
   }
]

Explanation

From souce JSON I need:

  • id from query.ids array and call new property counter_id
  • compare and join values from array query.dimensions and values from array data.dimensions, but give them new names. Values from query.dimensions should be property names for values from data.dimensions (we need this to keep right order). After this I want to change these names: ym:s:date should be date_of_visit, ym:s:lastSignUTMSource should be utm_source, ym:s:lastSignUTMMedium to utm_medium, ym:s:lastSignUTMCampaign to utm_campaign, ym:s:lastSignUTMContent to utm_content, ym:s:lastSignUTMTerm to utm_term.
  • compare and join values from query.metrics with values from array data.metrics. But each element from array query.metrics. f.e. ym:s:goal315094773visits should be written as goal_id: 315094773 (we need value between goal....visits). And value from data.metrics should be written as conversions.

So I expect to get result JSON like above presented.

I hard worked with it and got this JOLT config, but it doesn't even work. My knowledge is definetely not enough(

JOLT

Solution

  • You can flatten by the following transformation :

    [
  {
    "operation": "shift",
    "spec": {
      "data": { // loop through all the "data"
        "*": { // indexes of the "data"
          "metrics": { // loop through all the "metrics"
            "*": { // indexes of the "metrics"
              "@4,query.ids[0]": "&3_&.counter_id",
              "@2,dimensions[0].name": "&3_&.date_of_visit",
              "@2,dimensions[1].name": "&3_&.utm_source",
              "@2,dimensions[2].name": "&3_&.utm_medium",
              "@2,dimensions[3].name": "&3_&.utm_campaign",
              "@2,dimensions[4].name": "&3_&.utm_content",
              "@2,dimensions[5].name": "&3_&.utm_term",
              "@4,query.metrics[&]": {
                "*goal*visits": {
                  "$(0,2)": "&5_&2.goal_id" // extract the value of 2nd replacement of asterisks from the key just after going upper (0th)level
                }
              },
              "@": "&3_&.conversions" // the values of the "metrics"
            }
          }
        }
      }
    }
  },
  { // get rid of the object keys
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]