dataweavemule4

Dataweave / Mule : CSV to JSON - 3 level Hierarchy Issue


Source Data : Here is the CSV Data (4 Records):

EMP_ID,DEPT_ID,OPRT_UNIT_CODE,PROD_CODE,NODE_CODE,ALLOC_PCT
123456,00700,0101,000,00700-0101,5
123456,00700,0101,000,00700-0101-000,5
123456,02150,0101,000,2150-0101,95 
123456,02150,0101,000,02150-0101-000,95 

Below DW Code for your Reference.

Mule - Dataweave Code :

%dw 2.0
output application/json
---
{
data:payload groupBy $.EMP_ID mapObject ((value, key) -> {
    empID: value[0].EMP_ID,
    jobData: [ value groupBy ($.EMP_ID ++ '|' ++ $.DEPT_ID ++ '|' ++ $.OPRT_UNIT_CODE ++ '|' ++ $.PROD_CODE) mapObject ((subValue, subKey) -> {
      // jobData: subKey,
      DEPT_ID: subValue[0].DEPT_ID, 
      OPRT_UNIT_CODE: subValue[0].OPRT_UNIT_CODE,
      PROD_CODE: subValue[0].PROD_CODE,
      orgData: subValue map ((item, index) -> {
      OrgCode: item.NODE_CODE,
        AllocPct: item.ALLOC_PCT
      })
    })
    ]
  })
} 

Result - JSON Payload(Error: Duplicate key 'DEPT_ID'): Looks "jobData" data not correct missed "{ }", Experts, request you to please verify and correct the code.

{
  "data": {
    "empID": "123456",
    "jobData": [
      {
        "DEPT_ID": "00700",
        "OPRT_UNIT_CODE": "0101",
        "PROD_CODE": "000",
        "orgData": [
          {
            "OrgCode": "00700-0101",
            "AllocPct": "5"
          },
          {
            "OrgCode": "00700-0101-000",
            "AllocPct": "5"
          }
        ],
        "DEPT_ID": "02150",
        "OPRT_UNIT_CODE": "0101",
        "PROD_CODE": "000",
        "orgData": [
          {
            "OrgCode": "2150-0101",
            "AllocPct": "95"
          },
          {
            "OrgCode": "02150-0101-000",
            "AllocPct": "95"
          }
        ]
      }
    ]
  }
}

Expected Result - JSON Payload: Here is the expected result :

{
  "data":{
    "empID": "123456",
    "jobData": [
      {
        "DEPT_ID": "00700",
        "OPRT_UNIT_CODE": "0101",
        "PROD_CODE": "000",
        "orgData": [
          {
            "OrgCode": "00700-0101",
            "AllocPct": "5"
          },
          {
            "OrgCode": "00700-0101-000",
            "AllocPct": "5"
          }
        ]
       },
       {
        "DEPT_ID": "02150",
        "OPRT_UNIT_CODE": "0101",
        "PROD_CODE": "000",
        "orgData": [
          {
            "OrgCode": "2150-0101",
            "AllocPct": "95"
          },
          {
            "OrgCode": "02150-0101-000",
            "AllocPct": "95"
          }
        ]
       }
    ]
  }
}

Solution

  • Using mapObject() is replacing the key-values of the output of groupBy() by new key-values but that doesn't create new items in an array. Enveloping that output with literal array brackets ([ ... ]) also doesn't create new items, just an array with the single item object resulting of mapObject().

    The solution is simply to remove the literal array brackets and replace the use of mapObject() for pluck(). The function pluck() takes the key-values of an object and convert them to elements of an array with the provided mapping. That will give the expected output.