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",
}   
]

Hi @mahari. When you say 'flatten', do you mean a text 'flat file'? Both terms have different and specific meaning in DataWeave. `flatten()` is a function that is used to create a simple array from nested arrays and that doesn't has any relationship to your question. Flat files are fixed length text files, which your output doesn't seem to be either. What is exactly the JSON output, if the 'text' output is the expected one? Please also explain the mapping of the input JSON to the text output. Is the question about how to generate the expected output or how to actually load in the database? — aled, Dec 27 '22 at 12:30
Note that your input JSON is invalid because it misses some commas at the `Company` key. — aled, Dec 27 '22 at 12:47

score 0 · Accepted Answer · answered Dec 27 '22 at 12:46

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

Thank you so much Aled, much appreciated your solution!! trying to load "output data" into database. what method need to refer for data conversion csv to records and load into database. Insert into db_table(ContractNumber,Company|LineShipToCustomer,LineReferenceId,ContractName,ReferenceId) values ('1111111', 'ABCD INC.','GOOD INC.','123456789_EXP','TEST Contract','123456789'); .... insert till last record (looping / bulk insert) — Mahari, Dec 27 '22 at 17:09
You should ask a different question for the database part and leave this question for the conversion to CSV. Note that there is no need to convert to CSV for inserting in a database. `application/java` would be a better output for that. Please feel free to accept/upvote the answer to signal it is valid to the community. — aled, Dec 27 '22 at 17:14
Thank you so much!! Code tested and worked !!! great support !!! — Mahari, Dec 27 '22 at 17:58

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

1 Answers1