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

fujidaon
January 20, 2024
173 views
0 votes
2 Answers

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(

Tags: jolt json

Answers

Not sure if this is the most efficient way but it does the job and its dynamic regardless how many query dimensions you have or how many data metrics you have. Only condition is the query dimension order coincide with data dimension order to match each value with its field based on that order:

[
  {
    //prepare fields names in query dimensions by
    //concat utm_ prefix to them changing to lower case
    "operation": "modify-overwrite-beta",
    "spec": {
      "query": {
        "dimensions": {
          "*": "=concat('utm_',@(0))"
        },
        "dimensionJoin": "=join(',',@(1,dimensions))",
        "dimensionsLowercase": "=toLower(@(1,dimensionJoin))",
        "fields": "=split('[,]',@(1,dimensionsLowercase))"
      }
    }
  }
  ,
  {
    //extract query metrics values for global ids
    // apply each data metric value to above dimension, this 
    // will produce data object of 4 records where each record
    // contains 2 dimensions for each metric
    "operation": "shift",
    "spec": {
      "query": {
        "metrics": {
          "*": {
            "ym:s:goal*visits": {
              "$(0,1)": "query.metrics[&2]"
            }
          }
        }
      },
      "data": {
        "*": {
          "metrics": {
            "*": {
              "@(2,dimensions)": "data[&3].[&1].dimensions",
              "@": "data[&3].[&1].conversions"
            }
          }
        }
      }
    }
    }
 ,
  {
    // flatten the data nested arrays above utlizing
    // unique index combination of parent-child arrays
    // which we can group each record fields under
    "operation": "shift",
    "spec": {
      "data": {
        "*": {
          "*": {
            "dimensions": {
              "*": {
                "*": "&4_&3.@(6,query.fields.[&1])"
              }
            },
            "conversions": "&2_&1.&",
            "@(3,query.ids[0])": "&2_&1.counter_id",
            "@(3,query.metrics[&1])": "&2_&1.goal_id"
          }
        }
      }
    }
    }
  ,
  {
    // remove unique index grouping and bucket all
    // 8 records into one parent array
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
    }
   ,
  {
    //rename fields accordingly
    "operation": "shift",
    "spec": {
      "*": {
        "*date":"[&1].date_of_visit",
        "*ym:s:lastsignutm*": "[&1].&(0,1)&(0,2)",
        "*": "[&1].&"
      }
    }
    } /**/
]

Let me know if you have any questions.
Thanks

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

Please signup or login to give your own answer.

Click here to cancel reply.

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

Answers