Home > database >  Convert nested JSON convert to CSV with DataWeave in Mule 4
Convert nested JSON convert to CSV with DataWeave in Mule 4

Time:03-29

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;
  • Related