Convert a single column values into multiple rows using dataweave

Question

I receive a JSON payload where two columns contains array of values. We want to split the the array column into multiple rows.

Sample Input:

[
    {
        "firstName": "John",
        "surname": "Smith",
        "primarySkills": [
            "presentation",
            "developer"
        ],
        "secondarySkills": [
            "abc",
            "xyz"
        ],
        "email": "[email protected]",
        "phone": "1234567890"
    },
    {
        "firstName": "S",
        "surname": "D",
        "primarySkills": [
            "presentation"
        ],
        "secondarySkills": [
            "developer"
        ],
        "email": "[email protected]",
        "phone": "1234567890"
    }
]

Expect Output:

[
    {
        "firstName": "John",
        "surname": "Smith",
        "primarySkills": "presentation",
        "secondarySkills": "abc",
        "email": "[email protected]",
        "phone": "1234567890"
    },
    {
        "firstName": "John",
        "surname": "Smith",
        "primarySkills": "developer",
        "secondarySkills": "xyz",
        "email": "[email protected]",
        "phone": "1234567890"
    }
    {
        "firstName": "S",
        "surname": "D",
        "primarySkills": "presentation",
        "secondarySkills": "developer",
        "email": "[email protected]",
        "phone": "1234567890"
    }
]

Can someone help me how this can be achieved.

Thank you in advance

Answer 1

Assuming that both nested arrays have the same length you can map each skill into its parent record and add the second using the index of the first skill. Using flatMap() takes cares of unneeded nested arrays.

%dw 2.0
output application/json
---
payload flatMap ((item) -> 
    (item.primarySkills flatMap ((skill, index) -> 
        item - "primarySkills"  - "secondarySkills" 
            ++ {primarySkills: skill} 
            ++ {secondarySkills: item.secondarySkills[index]})
    )
)

Output:

[
  {
    "firstName": "John",
    "surname": "Smith",
    "email": "[email protected]",
    "phone": "1234567890",
    "primarySkills": "presentation",
    "secondarySkills": "abc"
  },
  {
    "firstName": "John",
    "surname": "Smith",
    "email": "[email protected]",
    "phone": "1234567890",
    "primarySkills": "developer",
    "secondarySkills": "xyz"
  },
  {
    "firstName": "S",
    "surname": "D",
    "email": "[email protected]",
    "phone": "1234567890",
    "primarySkills": "presentation",
    "secondarySkills": "developer"
  }
]