I'm trying to write a spec to do the below transformation using jolt transformation. I need to convert the nestedd JSON to flat JSON
I am having some trouble with converting the convert the nested JSON to Flat JSON I have looked at examples and didn't get any closer as to what is mentioned above. I need to transform a JSON structure by using a JOLT spec. I use https://jolt-demo.appspot.com to test the following below.
Input 1 Case where Sales Area is a List there is no nested Partner Function
{
"CustomerMaster": {
"Rootnode": {
"KUNNR": "0006503338",
"NAME1": "Schwarz Unternehmens Treuhand KG",
"LAND1": "DK",
"SalesArea": [
{
"VKORG": "1301",
"VTWEG": "10",
"SPART": "00"
},
{
"VKORG": "1501",
"VTWEG": "10",
"SPART": "00"
}
],
"Indicator": "",
"TimeStamp": "2024-12-18T14:04:40Z"
}
}
}
Expected Output 1:
I want the flattened JSON
[ {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1301",
"VTWEG" : "10",
"SPART" : "00"
}, {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1501",
"VTWEG" : "10",
"SPART" : "00",
}
} ]
Input 2 Case where Sales Area is not a List and one element which is can contain a list of Partner Function
{
"CustomerMaster": {
"Rootnode": {
"KUNNR": "123456789",
"NAME1": "Milchwerke \"Mittelelbe\" GmbH",
"NAME2": "Milchwerke \"Mittelelbe\" GmbH",
"NAME3": "Milchwerke \"Mittelelbe\" GmbH",
"NAME4": "Milchwerke \"Mittelelbe\" GmbH",
"LAND1": "DE",
"SalesArea": {
"VKORG": "1301",
"VTWEG": "00",
"SPART": "00",
"PartnerFunction": [
{
"PARVW": "WE",
"PARZA": "000",
"KUNN2": "0000045959"
},
{
"PARVW": "AG",
"PARZA": "000",
"KUNN2": "0000047480"
}
]
}
}
}
}
Expected Output 2 :
[ {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1301",
"VTWEG" : "10",
"SPART" : "00",
"PARVW" : "WE",
"PARZA" : "000",
"KUNN2" : "0000045959"
}, {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1301",
"VTWEG" : "10",
"SPART" : "00",
"PARVW" : "AG",
"PARZA" : "000",
"KUNN2" : "0000047480"
} ]
Input 3
Case where Sales Area is a List and each element can contain a list of Partner Function
{
"CustomerMaster": {
"Rootnode": {
"KUNNR": "0006503338",
"NAME1": "Schwarz Unternehmens Treuhand KG",
"LAND1": "DK",
"SalesArea": [
{
"VKORG": "1301",
"VTWEG": "10",
"SPART": "00",
"PartnerFunction": [
{
"PARVW": "WE",
"PARZA": "000",
"KUNN2": "0000045959"
},
{
"PARVW": "AG",
"PARZA": "000",
"KUNN2": "0000047480"
}
]
},
{
"VKORG": "1501",
"VTWEG": "10",
"SPART": "00",
"PartnerFunction": [
{
"PARVW": "WE",
"PARZA": "000",
"KUNN2": "0000045959"
},
{
"PARVW": "AG",
"PARZA": "000",
"KUNN2": "0000047480"
}
]
}
],
"Indicator": "",
"TimeStamp": "2024-12-18T14:04:40Z"
}
}
}
Expected Output 3:
[
{
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1301",
"VTWEG" : "10",
"SPART" : "00",
"PARVW" : "WE",
"PARZA" : "000",
"KUNN2" : "0000045959"
}, {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1301",
"VTWEG" : "10",
"SPART" : "00",
"PARVW" : "AG",
"PARZA" : "000",
"KUNN2" : "0000047480"
}, {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1501",
"VTWEG" : "10",
"SPART" : "00",
"PARVW" : "WE1",
"PARZA" : "000",
"KUNN2" : "0000045959"
}, {
"KUNNR" : "0006503338",
"NAME1" : "Schwarz Unternehmens Treuhand KG",
"LAND1" : "DK",
"Indicator" : "",
"TimeStamp" : "2024-12-18T14:04:40Z",
"VKORG" : "1501",
"VTWEG" : "10",
"SPART" : "00",
"PARVW" : "AG1",
"PARZA" : "000",
"KUNN2" : "0000047480"
} ]
This spec will be able to handle all the formats
[
{
"operation": "cardinality",
"spec": {
"CustomerMaster": {
"Rootnode": {
"SalesArea": "MANY"//make SalesArea always as an array
}
}
}
},
{
"operation": "cardinality",
"spec": {
"CustomerMaster": {
"Rootnode": {
"SalesArea": {
"*": {
"PartnerFunction": "MANY"//make PartnerFunction always as an array
}
}
}
}
}
},
{
"operation": "default",
"spec": {
"CustomerMaster": {
"Rootnode": {
"SalesArea[]": {
"*": {
"PartnerFunction": [], //initialize a default PartnerFunction array if it's missing
"PartnerFunction[]": {
"0": { //initialize a default PartnerFunction object at index 0 using a dummy field named initializer
"initializer": "placeholder"
}
}
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"CustomerMaster": {
"Rootnode": {
"SalesArea": {
"*": {
"PartnerFunction": {
"*": {// Map the fields starting from PartnerFunction array
"PARVW": "[&3].[&1].&",
"PARZA": "[&3].[&1].&",
"KUNN2": "[&3].[&1].&",
"@(2,VKORG)": "[&3].[&1].VKORG", // Go 2 level up to map SalesArea fields
"@(2,VTWEG)": "[&3].[&1].VTWEG",
"@(2,SPART)": "[&3].[&1].SPART",
"@(4,KUNNR)": "[&3].[&1].KUNNR", // Go 4 level up to map Rootnode fields
"@(4,NAME1)": "[&3].[&1].NAME1",
"@(4,LAND1)": "[&3].[&1].LAND1"
}
}
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"*": "" // Unwrap the grouped objects
}
}
}
]
The key here is to iterate over PartnerFunction and then map the outer fields to it. For that, first the SalesArea and PartnerFunction objects need to normalized as an Array using Cardinality operation.
Below are the functions of each operation in this spec
Cardinality to make SalesArea always as an arrayCardinality to make PartnerFunction always as an arrayDefault operation to inialize a PartnerFunction object within the array so that it can be used as an anchor for mappingShift for mapping all the fields using PartnerFunction as the anchorShift for unwrapping the SalesArea grouped array