Mule 4 : Convert nested JSON and load into database (flatten)

Question

I need to convert below nested JSON into flatten and load into database.

Nested JSON into Flatten

JSON Input:

{
"Report_Entry": [
{
   "ContractNumber": "1111111",
   "Company": "ABCD INC."
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "GOOD INC.",
         "LineReferenceId": "123456789_EXP"
      },
      {
         "LineShipToCustomer": "XYZ TELECOM",
         "LineReferenceId": "123456789_TIME"
      }
   ],
   "ContractName": "TEST Contract",
   "ReferenceId": "123456789"
},
{
   "ContractNumber": "222222",
   "Company": "FASLSE NEWS INC."
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "LIVE NEWS INC.",
         "LineReferenceId": "789999_EXP"
      },
      {
         "LineShipToCustomer": "SKY NEWS INC.",
         "LineReferenceId": "789999_TIME"
      }
   ],
   "ContractName": "FALSE NEWS Contract",
   "ReferenceId": "6789999"
}
]
}

Expected TEXT Output:

ContractNumber|Company|LineShipToCustomer|LineReferenceId|ContractName|ReferenceId

\-- Set 1

1111111|ABCD INC|GOOD INC|123456789_EXP|TEST Contract|123456789
1111111|ABCD INC|XYZ TELECOM|123456789_TIME|TEST Contract|123456789

\-- Set 2

222222|FASLSE NEWS INC.|LIVE NEWS INC.|789999_EXP|FALSE NEWS Contract|6789999
222222|FASLSE NEWS INC.|SKY NEWS INC.|789999_TIME|FALSE NEWS Contract|6789999

Output (JSON):

[
{
   "ContractNumber": "1111111",
   "Company": "ABCD INC."
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "GOOD INC.",
         "LineReferenceId": "123456789_EXP"
      }
   ],
   "ContractName": "TEST Contract",
   "ReferenceId": "123456789"
}, 
{
   "ContractNumber": "1111111",
   "Company": "ABCD INC."
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "XYZ TELECOM",
         "LineReferenceId": "123456789_TIME"
      }
   ],
   "ContractName": "TEST Contract",
   "ReferenceId": "123456789"
   } ,
{
   "ContractNumber": "222222",
   "Company": "FASLSE NEWS INC."
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "LIVE NEWS INC.",
         "LineReferenceId": "789999_EXP"
      }
   ],
   "ContractName": "FALSE NEWS Contract",
   "ReferenceId": "123456789",
},
{
   "ContractNumber": "222222",
   "Company": "FASLSE NEWS INC."
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "SKY NEWS INC.",
         "LineReferenceId": "789999_TIME"
      }
   ],
   "ContractName": "FALSE NEWS Contract",
   "ReferenceId": "6789999",
}   
]

Answer 1

I assume that you need to convert the input JSON into a CSV output using the pipe character | as a separator. You need to map each of the line internal items into a record.

You could use the flatten() function however Mule 4 has a more convenient function to map and flatten at the same time: flatMap().

Example:

%dw 2.0
output application/csv separator="|"
---
payload.Report_Entry flatMap ((item, order) -> 
    item.Contract_Lines_group map ((line, lineOrder) -> {
        ContractNumber: item.ContractNumber,
        Company: item.Company,
        LineShipToCustomer: line.LineShipToCustomer,
        LineReferenceId: line.LineReferenceId,
        ContractName: item.ContractName,
        ReferenceId: item.ReferenceId
    })
)

Output:

ContractNumber|Company|LineShipToCustomer|LineReferenceId|ContractName|ReferenceId
1111111|ABCD INC.|GOOD INC.|123456789_EXP|TEST Contract|123456789
1111111|ABCD INC.|XYZ TELECOM|123456789_TIME|TEST Contract|123456789
222222|FASLSE NEWS INC.|LIVE NEWS INC.|789999_EXP|FALSE NEWS Contract|6789999
222222|FASLSE NEWS INC.|SKY NEWS INC.|789999_TIME|FALSE NEWS Contract|6789999