Source JSON:
{
"data": {
"campaign": {
"104": {
"day": {
"2024-07-01": {
"metrics": {
"reach_impression": "6.0000000",
"unique_impression": 6,
"impression": 9
}
},
"2024-07-03": {
"metrics": {
"reach_impression": "19.0000000",
"unique_click": 1,
"reach_click": "2.0000000"
}
}
}
},
"106": {
"day": {
"2024-07-01": {
"metrics": {
"unique_click": 29,
"reach_click": "29.0000000",
"click": 35
}
}
}
},
"124": {
"day": {
"2024-07-01": {
"metrics": {
"unique_impression": 472,
"event": 1,
"unique_click": 25,
"click": 49,
"progress_100": 1,
"impression": 690
}
},
"2024-07-03": {
"metrics": {
"unique_click": 14,
"reach_click": "52.0000000",
"click": 33,
"unique_impression": 28,
"impression": 32
}
}
}
}
},
"metrics": {
"click_average": 22107.6666666667
}
},
"metadata": {
"campaign": {
"104": {
"channel_id": 1,
"real_end_date": null,
"channel_label": "Display",
"real_start_date": "2024-01-11 21:32:25",
"label": "5ka_Promo_veer_2024"
},
"124": {
"channel_id": 1,
"real_end_date": null,
"channel_label": "Display",
"real_start_date": "2024-06-04 21:34:23",
"label": " X5Club_Evergreen_June24"
}
}
}
}
Under data.campaign there are some numbers like 124, 104, 106. These numbers – ids of campaigns. I need to store these values inside field id, then we have field day and inside this field values like 2024-07-01 and i want to store these values as report_date field. Also under this level we have metrics object. Inside this object I want to grab all key-value pairs.
Main problem for me, how to store just numbers as keys in the right way.
Expected JSON:
[
{
"id": 124,
"report_date": "2024-07-01",
"unique_impression": 472,
"event": 1,
"unique_click": 25,
"click": 49,
"progress_100": 1,
"impression": 690
},
{
"id": 124,
"report_date": "2024-07-03",
"unique_click": 14,
"reach_click": "52.0000000",
"click": 33,
"unique_impression": 28,
"impression": 32
},
{
"id": 104,
"report_date": "2024-07-01",
"reach_impression": "6.0000000",
"unique_impression": 6,
"impression": 9
},
{
"id": 104,
"report_date": "2024-07-03",
"reach_impression": "19.0000000",
"unique_click": 1,
"reach_click": "2.0000000"
},
{
"id": 106,
"report_date": "2024-07-01",
"unique_click": 29,
"reach_click": "29.0000000",
"click": 35
}
]
UPDATE
I also want to add field label from metadata object. I should match id from data.campaign.[number id] with metadata.campaign.[number id] and call it campaign_name
Expect:
[
{
"id": 124,
"report_date": "2024-07-01",
"unique_impression": 472,
"event": 1,
"unique_click": 25,
"click": 49,
"progress_100": 1,
"impression": 690,
"campaign_name": "X5Club_Evergreen_June24"
},
{
//etc
You can use the following transfomation in which $ wildcards(on the left hand side(s)) replicate the object keys taken from their respective levels and constructs the key-value pairs through the leaf node literals stated on the right-hand-side(s) such as :
[
{
"operation": "shift",
"spec": {
"data": {
"campaign": {
"*": {
"day": {
"*": {
"metrics": {
"$3": "&4_&2.id",
"$1": "&4_&2.report_date",
"*": "&4_&2.&"
}
}
}
}
}
}
}
},
{//make id values unquoted
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"id": "=toInteger"
}
}
},
{//get rid of object keys
"operation": "shift",
"spec": {
"*": "[]"
}
}
]
Edit : Yes, it’s possible, just add
"@6,metadata.campaign.&3.label": "&4_&2.&5_name"
pair into the "metrics" objects such as
"metrics": {
"$3": "&4_&2.id",
"$1": "&4_&2.report_date",
"*": "&4_&2.&",
"@6,metadata.campaign.&3.label": "&4_&2.&5_name"
}
in which
@6makes path start from the level of"data"object which is at the same as the level with the"metadata"&3replicates< number id >undermetadata.campaign.&5on the RHS replicates the literalcampaignfor the&5_name
expression.
2