Mule Nested JSON data issue - If Child does not exists in data mule output parent and child columns data NULL

Question

Mule Nested JSON data issue - If Child does not exists in data mule output parent and child columns data NULL. How to capture parent columns data if child data not exists.

JSON Data :

{
"Report_Entry": [{
   "p_sname": "TEST_1",
   "p_sno": "1234",
    "SUBJECT_DATA_GROUP": [
      {
         "sub_name": "social",
         "sub_marks: "80"
      },
      {
         "sub_name": "science",
         "sub_marks: "60"
      },
      {
         "sub_name": "maths",
         "sub_marks: "90"
      }
     ],
   "p_s_start_date": "10-10-1991",
   "p_s_status": "A"
      
},
{
   "p_sname": "TEST_2",
   "p_sno": "9999",
    "SUBJECT_DATA_GROUP": [
      {
         "sub_name": "social",
         "sub_marks: "30"
      },
      {
         "sub_name": "science",
         "sub_marks: "40"
      },
      {
         "sub_name": "maths",
         "sub_marks: "50"
      }
     ],
   "p_s_start_date": "10-10-1999",
   "p_s_status": "A"
},
{
   "p_sname": "TEST_3",
   "p_sno": "8888",
   "p_s_start_date": "10-10-2000",
   "p_s_status": "A"
},
{
   "p_sname": "TEST_4",
   "p_sno": "6666",
   "p_s_start_date": "10-10-2020",
   "p_s_status": "A"
}
]
}

%dw 2.0
output application/java
---
payload.Report_Entry flatMap ((item, order) -> 
    item.SUBJECT_DATA_GROUP map ((line, lineOrder) -> {
    //Main
       "p_sname": item.p_sname,
       "p_sno": item.p_sno,
       "Hours": item.Hours,
    //Line
         "sub_name": line.sub_name,
         "sub_marks": line.sub_marks,
    //Main
     "p_s_start_date": item.p_s_start_date,
     "p_s_status": item.p_s_status
        }
    )
)

Result :

p_sname|p_sno|sub_name|sub_marks|p_s_start_date|p_s_status
TEST_1|1234|social|80|10-10-1991|A
TEST_1|1234|science|60|10-10-1991|A
TEST_1|1234|maths|90|10-10-1991|A
TEST_2|9999|maths|30|10-10-1999|A
TEST_2|9999|maths|40|10-10-1999|A
TEST_2|9999|maths|50|10-10-1999|A
null|null|null|null|null|null
null|null|||null|null

Expected Result :

p_sname|p_sno|sub_name|sub_marks|p_s_start_date|p_s_status
TEST_1|1234|social|80|10-10-1991|A
TEST_1|1234|science|60|10-10-1991|A
TEST_1|1234|maths|90|10-10-1991|A
TEST_2|9999|maths|30|10-10-1999|A
TEST_2|9999|maths|40|10-10-1999|A
TEST_2|9999|maths|50|10-10-1999|A
TEST_3|8888|||10-10-2000|A
TEST_4|6666|||10-10-2020|A

Answer 1

You can use default keyword to set array with an empty object to handle scenarios where SUBJECT_DATA_GROUP doesn't exists.

I have used application/csv as the mimetype to match the given output.

%dw 2.0
output application/csv separator="|"
---
payload.Report_Entry flatMap ((item) -> 
    item.SUBJECT_DATA_GROUP default [{}] map ((line) -> {
        p_sname: item.p_sname,
        p_sno: item.p_sno,
        Hours: item.Hours,              
        sub_name: line.sub_name,
        sub_marks: line.sub_marks,
        p_s_start_date: item.p_s_start_date,
        p_s_status: item.p_s_status                 
    }) 
)