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

aled · Accepted Answer · 2024-03-22 20:36:02Z

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

sudhish_s · Accepted Answer · 2024-03-26 13:24:38Z

1

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
        }
    )
)

answered Mar 26, 2024 at 13:24

sudhish_s

6283 silver badges5 bronze badges

Collectives™ on Stack Overflow

Convert a single column values into multiple rows using dataweave

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related