I have this transform message:
%dw 2.0
output application/json
---
{
"one": [
"two": payload.one.two,
"three": payload.one.three,
"four": {
"five": payload.one.four.five map (item, index) -> {
five : item
as DateTime
{format: "yyyy-MM-dd'T'HH:mm:ss.SSSxx"}
as String {format: "yyyy-MM-dd"}
},
"six":{
"seven": payload.one.four.six.seven,
"eight": payload.one.four.six.eight
},
"nine": {
"name": payload.one.four.nine.name
},
"tenCreated":payload.one.four.tenCreated map (item, index) -> {
tenCreated : item
as DateTime
{format: "yyyy-MM-dd'T'HH:mm:ss.SSSxx"}
as String {format: "yyyy-MM-dd"},
},
"elevenUpdated": payload.one.four.elevenUpdated map (item, index) -> {
elevenUpdated : item
as DateTime
{format: "yyyy-MM-dd'T'HH:mm:ss.SSSxx"}
as String {format: "yyyy-MM-dd"}
},
"twelve": {
"thirteen": payload.one.four.twelve.thirteen
},
"fifteen": {
"name": payload.one.four.fifteen.name
},
"sixteen":{
"sixteenCreator": payload.one.four.sixteen.sixteenCreator,
"seventeen": payload.four.one.sixteen.seventeen
},
"eieighteen": payload.one.four.eieighteen,
"ninteen: {
"ninteeneReporter": payload.one.four.ninteen.ninteeneReporter,
"twentyEmail": payload.one.four.ninteen.twentyEmail
},
"twentyOne":{
"TwentyTwo": payload.one.four.twentyOne.TwentyTwo,
"TwnetyThree": payload.one.four.twentyOne.TwnetyThree
},
"TwentyFour": {
"TwentyFive": payload.one.four.TwentyFour.TwentyFive,
"TwentySix":{
"TwentySeven": payload.one.four.TwentyFour.TwentySix.TwentySeven,
"TwentyEight": payload.one.four.TwentyFour.TwentySix.TwentyEight.name,
"Thiry": payload.one.four.TwentyFour.TwentySix.Thiry.name,
"ThirtyOne": payload.isuues.four.TwentyFour.TwentySix.ThirtyOne.name
}
}
}
] reduce (element,acc={}) -> acc element
}
This transform message as output looks like this:
{
"one": {
"two": [
"199052",
"109926"
],
"three": [
"191",
"190",
"189",
"188",
"187"
],
"four": {
"five": [
{
"five": "2022-03-24"
},
{
"five": "2022-03-24"
},
{
"five": "2022-03-18"
},
{
"five": "2022-03-18"
},
{
"five": "2022-03-18"
},
{
"five": "2022-03-14"
},
{
"five": "2022-03-14"
},
],
"six": {
"seven": [
"Test1",
"Test2",
"Test1",
"Test2"
],
"eight": [
"first description.",
"second description",
"first description.",
"second description"
]
},
"nine": {
"name": [
"Ps3564",
"35355Ps"
]
},
"tenCreated": [
{
"tenCreated": "2022-02-10"
},
{
"tenCreated": "2022-02-10"
},
{
"tenCreated": "2022-02-10"
}
],
"elevenUpdated": [
{
"elevenUpdated": "2022-03-24"
},
{
"elevenUpdated": "2022-03-24"
},
{
"elevenUpdated": "2022-03-24"
},
],
"twelve": {
"thirteen": [
"fourteen",
"Do",
"Do again",
"Do work",
"Doone"
]
},
"fifteen": {
"name": [
"Good",
"Not good",
"good"
]
},
"sixteen": {
"sixteenCreator": [
"Jan Kowalski",
"Jan kowalski",
"Jan Kowalski",
"Jan kowalski"
],
"seventeen": null
},
"eieighteen": [
"Test test",
"test test 10",
"api test",
],
"ninteen": {
"ninteeneReporter": [
"Jan Kowalski",
"Jan Kowalski",
"Jan Kowalski"
],
"twentyEmail": [
"jankowalski@",
"jankowalski@"
]
},
"twentyOne": {
"TwentyTwo": [
"Jan Kowalski",
"Jan Kowalski",
"Jan Kowalski"
],
"TwnetyThree": [
"jankowalski@",
"jankowalski@"
]
},
"TwentyFour": {
"TwentyFive": [
"P186",
"P186",
"P186",
],
"TwentySix": {
"TwentySeven": [
"Plan",
"Plan",
"Plan"
],
"TwentyEight": [
"END",
"END"
],
"Thiry": [
"To Do",
"To Do",
"To Do",
"To Do",
],
"ThirtyOne": null
}
}
}
}
}
How I can convert this structure as CSV using transform message ?
I would like that my csv looks like this (it's only example):
two;three;four;five
199052;191;2022-03-24
109926;191;2022-03-24
190;2022-03-24
189;2022-03-24
188;2022-03-24
187;2022-03-24
2022-03-24
CodePudding user response：
I'm assuming that the output should be consistent so I combined some of my previous answers and created this reusable function to generalize the method of generating a CSV (or record based output) from arrays of different lengths.
%dw 2.0
output application/csv separator=";", header=true
// Converts an object with each value is an array to an array with key pairs useful to be used for emitting a CSV output
fun ObjectToRecords(o)=do {
var columns = namesOf(o)
var maxColumnSize = max(columns map sizeOf(o[$]))
var maxColumnName = columns dw::core::Arrays::firstWith (sizeOf(o[$]) == maxColumnSize)
---
o[maxColumnName]
map ((item, index) -> (
columns map ($): o[$][index])
reduce ($$ $)
)
}
var payload2 = {
two: payload.one.two,
three: payload.one.three,
four: payload.one.four.five.five,
five: []
}
---
ObjectToRecords(payload2)
Output:
two;three;four;five
199052;191;2022-03-24;
109926;190;2022-03-24;
;189;2022-03-18;
;188;2022-03-18;
;187;2022-03-18;
;;2022-03-14;
;;2022-03-14;