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

Answer 2

Assuming PrimarySkills always present, always have the more number of skills than the secondary skills and using same Index as Primary Skills to pick the secondary skills, you can use the flatMap and update operator combination.

SecondarySkils will only be mapped if exists.

%dw 2.0
output application/json skipNullOn="everywhere"
---
payload flatMap ((item) -> 
    item.primarySkills map ((skill, index) -> 
        item update {
            case ps at .primarySkills -> skill
            case ss at .secondarySkills if (! isEmpty(ss)) -> if (! isEmpty(ss[index])) ss[index] else null
        }
    )
)