I am new to JOLT Transformation and I found it really useful in JSON Transformations. But when i come across with a nested and complex JSON I get confused.
The following JSON is a nested and complex JsonArray that I need to transform it into a completely Flat JsonArray.
JSON Input:
[
{
"Code": -1,
"Name": "All",
"PublisherTypes": [
{
"Code": 2,
"Name": "General",
"LetterTypes": []
},
{
"Code": 3,
"Name": "Financials",
"LetterTypes": []
}
]
},
{
"Code": 1,
"Name": "Information",
"PublisherTypes": [
{
"Code": 2,
"Name": "General",
"LetterTypes": [
{
"Id": 10,
"Name": "Long Term Info",
"Code": ""
},
{
"Id": 20,
"Name": "Short Term Info",
"Code": ""
}
]
},
{
"Code": 3,
"Name": "Financials",
"LetterTypes": [
{
"Id": 101,
"Name": "Total Income",
"Code": ""
},
{
"Id": 202,
"Name": "Total Taxes",
"Code": ""
}
]
}
]
},
{
"Code": 2,
"Name": "Reporting",
"PublisherTypes": [
{
"Code": 2,
"Name": "General",
"LetterTypes": [
{
"Id": 8,
"Name": "Monthly Reoprt",
"Code": ""
},
{
"Id": 58,
"Name": "Status Report",
"Code": ""
}
]
},
{
"Code": 3,
"Name": "Financials",
"LetterTypes": [
{
"Id": 170,
"Name": "Manager Level",
"Code": ""
},
{
"Id": 156,
"Name": "Expert Level",
"Code": ""
}
]
}
]
}
]
And the following JSON is my Expected Output.
Expected Output:
[
{
"Code": -1,
"Name": "All",
"PublisherCode": 2,
"PublisherName": "General",
"LetterId": "",
"LetterName": "",
"LetterCode": ""
},
{
"Code": -1,
"Name": "All",
"PublisherCode": 3,
"PublisherName": "Financials",
"LetterId": "",
"LetterName": "",
"LetterCode": ""
},
{
"Code": 1,
"Name": "Information",
"PublisherCode": 2,
"PublisherName": "General",
"LetterId": 10,
"LetterName": "Long Term Info",
"LetterCode": ""
},
{
"Code": 1,
"Name": "Information",
"PublisherCode": 2,
"PublisherName": "General",
"LetterId": 20,
"LetterName": "Short Term Info",
"LetterCode": ""
},
{
"Code": 1,
"Name": "Information",
"PublisherCode": 3,
"PublisherName": "Financials",
"LetterId": 101,
"LetterName": "Total Income",
"LetterCode": ""
},
{
"Code": 1,
"Name": "Information",
"PublisherCode": 3,
"PublisherName": "Financials",
"LetterId": 202,
"LetterName": "Total Taxes",
"LetterCode": ""
},
{
"Code": 2,
"Name": "Reporting",
"PublisherCode": 2,
"PublisherName": "General",
"LetterId": 8,
"LetterName": "Monthly Reoprt",
"LetterCode": ""
},
{
"Code": 2,
"Name": "Reporting",
"PublisherCode": 2,
"PublisherName": "General",
"LetterId": 58,
"LetterName": "Status Reoprt",
"LetterCode": ""
},
{
"Code": 2,
"Name": "Reporting",
"PublisherCode": 3,
"PublisherName": "Financials",
"LetterId": 170,
"LetterName": "Manager Level",
"LetterCode": ""
},
{
"Code": 2,
"Name": "Reporting",
"PublisherCode": 3,
"PublisherName": "Financials",
"LetterId": 156,
"LetterName": "Expert Level",
"LetterCode": ""
}
]
What I need is a JOLT Spec to generate above Output for me. So, can anyone provide a JOLT Spec for this problem?
CodePudding user response：
You can use the following shift transformation after applying modify transformation spec, in which;
- toInteger converts a quoted value to a unquoted integer if it can such as
"12"->12or"12.7"->12, otherwise it stops processing silently, eg unquoted array brackets[ ]are considered to be convertible, while an array with embedded objects are not considered to be integer of course - We've got individual arrays(or lists) within the second step, and we wanna combine them under a common factor. &2 represents reaching up the two levels by traversing
{twice, [&] represents combining all stuff as array at the current level.
, in order to populate the empty arrays with default null("") values for each attributes of LetterTypes array such as
[
{
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"*": {
"*": {
"Letter*": ["=toInteger",
[
{
"Id": "",
"Name": "",
"Code": ""
}
]
]
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"*Types": { // * wildcard represents one or multiple characters, here "*Types" stands for "PublisherTypes"
"*": {
"*Types": {
"*": {
"@(4,Code)": "Code",
"@(4,Name)": "Name", // going 4 levels up to grab the desired value
"@(2,Code)": "PublisherCode",
"@(2,Name)": "PublisherName", // going 2 levels up to grab the desired value
"*": "&(2,1)&" // all attributes under the "LetterTypes" arrays, in &(2,1)& : 2 is for going 2 levels up, 1 represents grabbing the piece("Letter") where * is substituted, and the last & represents the value of the current key name("Code" or "Name")
}
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"*": {
"@": "[&].&2"
}
}
}
}
]
all the attributes are accumulated under the innermost part where the most repeats will occur throughout the whole JSON value.
In the first spec, all individual array with 10 components are determined, and the they're moved to their proper objects within the second spec.