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.idsarray and call new propertycounter_id - compare and join values from array
query.dimensionsand values from arraydata.dimensions, but give them new names. Values fromquery.dimensionsshould be property names for values fromdata.dimensions(we need this to keep right order). After this I want to change these names:ym:s:dateshould bedate_of_visit,ym:s:lastSignUTMSourceshould beutm_source,ym:s:lastSignUTMMediumtoutm_medium,ym:s:lastSignUTMCampaigntoutm_campaign,ym:s:lastSignUTMContenttoutm_content,ym:s:lastSignUTMTermtoutm_term. - compare and join values from
query.metricswith values from arraydata.metrics. But each element from arrayquery.metrics. f.e.ym:s:goal315094773visitsshould be written asgoal_id: 315094773 (we need value between goal....visits). And value fromdata.metricsshould be written asconversions.
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(
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": {
"*": ""
}
}
]
- Jackson deserialize generic class with field T as POJO or String array
- How to parse nested dictionaries into Pandas DataFrame
- Serialize a Dictionary as JSON object using DataContractJsonSerializer
- How can I parse these hashes turned string, from my Ruby on Rails API so that it's accessible as an object in Javascript?
- How to configure JSON deserialization options in .NET minimal API project
- JSON schema validation of optional fields using Golang
- How to generate multi-level decision tree from a JSON structure using Python?
- Laravel sends JSON headers in body
- python + json file saving
- Rest Spring boot converts a response with duplicate object to id of that object
- how can i scrape data efficiently and clean it
- How to map table references in a JSON file to corresponding values in an Excel file using Python?
- JavaScript Table Pagination Rendering JSON Data
- Iterating Json attributes and PSCustom Objects
- Pydantic v2 vs requests POST JSON - 'not serializable' nightmares
- JOLT to check the value if it is null keep it as it is else update the value based on the condition
- java json schema validation relative path not working (URI not found)
- Embedding a json image into a typst document
- How to import a JSON file in ECMAScript 6?
- Deserializing JSON list into class objects not able to extract attributes
- Array to JSON array of strings
- How to get JSON.NET to serialize date/time to ISO 8601?
- how to remove json object key and value.?
- Laravel casting JSON to array?
- Check for Table null in Jquery for JSON response
- Get Value From Json object contain table column using SQL Query
- How to specify the response_model in FastAPI on a non-default return?
- Single versus double quotes in json loads in Python
- React JS - Uncaught TypeError: this.props.data.map is not a function
- Using Enums while parsing JSON with GSON