DataWeave 2.0 nested array transformation

0
votes

I'm working on trying to get from the below source/target structures, using Dataweave 2.0. I've tried various ways using nested maps and reduce scripts but having some difficulty.

The pain point is the customFieldList, I want to simplify the overall structure by changing the root field to be scriptId (for simplicity right now).

Any advice/help would be very much appreciated.

Source structure

{
    "line": [{
            "lineNumber": 0,
            "credit": 17.98,
            "customFieldList": {
                "customField": [{
                        "internalId": "5780",
                        "scriptId": "custcol_del_externalid",
                        "value": "0000000111111"
                    },
                    {
                        "internalId": "1446",
                        "scriptId": "custcol_4601_witaxapplies",
                        "value": false
                    },
                    {
                        "internalId": "1837",
                        "scriptId": "custcol_nsts_gaw_col_approver",
                        "value": {
                            "internalId": "29540",
                            "name": "Alan Mcgee",
                            "typeId": "-4"
                        }
                    },
                    {
                        "internalId": "2648",
                        "scriptId": "custcol_foreign_expense",
                        "value": false
                    }
                ]

            }
        },
        {
            "lineNumber": 1,
            "debit": 17.98,
            "customFieldList": {
                "customField": [{
                        "internalId": "5780",
                        "scriptId": "custcol_del_externalid",
                        "value": "0000000111111"
                    },
                    {
                        "internalId": "1446",
                        "scriptId": "custcol_4601_witaxapplies",
                        "value": false
                    },
                    {
                        "internalId": "1837",
                        "scriptId": "custcol_nsts_gaw_col_approver",
                        "value": {
                            "internalId": "29540",
                            "name": "Joe Davis",
                            "typeId": "-4"
                        }
                    },
                    {
                        "internalId": "2648",
                        "scriptId": "custcol_foreign_expense",
                        "value": false
                    }
                ]

            }
        }

    ]
}

Target structure

{
    "line": [{
            "lineNumber": 0,
            "credit": 17.98,

            "custcol_del_externalid": {
                "value": "0000000111111",
                "internalId": "5780"
            },
            "custcol_4601_witaxapplies": {
                "value": false,
                "internalId": "1446"
            },
            "custcol_nsts_gaw_col_approver": {
                "value": false,
                "internalId1": "1837",
                "internalId2": "29540",
                "name": "Alan Mcgee",
                "typeId": "-4"
            },
            "custcol_foreign_expense": {
                "value": false,
                "internalId": "2648"
            }
        },
        {
            "lineNumber": 1,
            "debit": 17.98,

            "custcol_del_externalid": {
                "value": "0000000111111",
                "internalId": "5780"
            },
            "custcol_4601_witaxapplies": {
                "value": false,
                "internalId": "1446"
            },
            "custcol_nsts_gaw_col_approver": {
                "value": false,
                "internalId1": "1837",
                "internalId2": "29540",
                "name": "Joe Davis",
                "typeId": "-4"
            },
            "custcol_foreign_expense": {
                "value": false,
                "internalId": "2648"
            }
        }

    ]
}
3
arraysdataweavemulesoft
Please clarify how the output custcol_nsts_gaw_col_approver should be constructed. It is confusing.why value is false? Are the number of ids fixed? Where they are coming from? - aled

3 Answers

3
votes

The structure is probably somewhat complex and it would be better to simplify it, but the real problem I found is trying to add multiple internalIds from the other entries in customField. I'm sharing the script that I got that doesn't resolve that part but seems to match the rest of the output:

%dw 2.0
output application/json skipNullOn="objects"
---
{
    line: payload.line map (field, index1) -> {
        lineNumber: field.lineNumber,
        credit: field.credit,
        debit: field.debit,
        (field.customFieldList.customField map (val, index2) ->
                {
                    (val.scriptId): {
                        value: val.value,
                        internalId: val.internalId
                    }
                }
        )    
    }
}

Output:

{
  "line": [
    {
      "lineNumber": 0,
      "credit": 17.98,
      "custcol_del_externalid": {
        "value": "0000000111111",
        "internalId": "5780"
      },
      "custcol_4601_witaxapplies": {
        "value": false,
        "internalId": "1446"
      },
      "custcol_nsts_gaw_col_approver": {
        "value": {
          "internalId": "29540",
          "name": "Alan Mcgee",
          "typeId": "-4"
        },
        "internalId": "1837"
      },
      "custcol_foreign_expense": {
        "value": false,
        "internalId": "2648"
      }
    },
    {
      "lineNumber": 1,
      "debit": 17.98,
      "custcol_del_externalid": {
        "value": "0000000111111",
        "internalId": "5780"
      },
      "custcol_4601_witaxapplies": {
        "value": false,
        "internalId": "1446"
      },
      "custcol_nsts_gaw_col_approver": {
        "value": {
          "internalId": "29540",
          "name": "Joe Davis",
          "typeId": "-4"
        },
        "internalId": "1837"
      },
      "custcol_foreign_expense": {
        "value": false,
        "internalId": "2648"
      }
    }
  ]
}

It may be resolved, however I don't have more time right now. If you can simplify the structure or the output it would be easier.

To be quicker I used the writer property skipNullOn to avoid emitting the attributes credit and debit when they are null.

0
votes

Let me start by saying your output is missing Joe Davis you show twice Alan Mcgee, I assume a typo.

I am making certain assumptions to get it to work. My assumptions are that (1) if value is an object then there is an internalId field and (2) there is a single nesting taking place from the value field--i.e. no more than that.

I could probably simplify it a little bit or expand on it--I just don't have the time now.

FYI: validate this is what you need, I do not have the time to do an exhaustive check.

Here's my code, it is self-contained so just copy and paste in a Transform Message processor.

EDIT: I got me a bit of extra time, changed the expression a little, and added comments to explain what the expression does. I hope it helps.

%dw 2.0
output application/dw

var data = {
    "line": [{
            "lineNumber": 0,
            "credit": 17.98,
            "customFieldList": {
                "customField": [{
                        "internalId": "5780",
                        "scriptId": "custcol_del_externalid",
                        "value": "0000000111111"
                    },
                    {
                        "internalId": "1446",
                        "scriptId": "custcol_4601_witaxapplies",
                        "value": false
                    },
                    {
                        "internalId": "1837",
                        "scriptId": "custcol_nsts_gaw_col_approver",
                        "value": {
                            "internalId": "29540",
                            "name": "Alan Mcgee",
                            "typeId": "-4"
                        }
                    },
                    {
                        "internalId": "2648",
                        "scriptId": "custcol_foreign_expense",
                        "value": false
                    }
                ]

            }
        },
        {
            "lineNumber": 1,
            "debit": 17.98,
            "customFieldList": {
                "customField": [{
                        "internalId": "5780",
                        "scriptId": "custcol_del_externalid",
                        "value": "0000000111111"
                    },
                    {
                        "internalId": "1446",
                        "scriptId": "custcol_4601_witaxapplies",
                        "value": false
                    },
                    {
                        "internalId": "1837",
                        "scriptId": "custcol_nsts_gaw_col_approver",
                        "value": {
                            "internalId": "29540",
                            "name": "Joe Davis",
                            "typeId": "-4"
                        }
                    },
                    {
                        "internalId": "2648",
                        "scriptId": "custcol_foreign_expense",
                        "value": false
                    }
                ]

            }
        }

    ]
}

---
// Create a new object with a single field named `line`
// Iterate over the array in the `data.line`
line: data.line map {  // Create an object where
    // you remove the `customFieldList` from the inputs while maintaining the other fields
    ($ - "customFieldList"),
    // Create an object with field names the values of the `scriptId` field.
    (do { // Create a localized declaration (aka closure) changing the array into an object
          // where each field is the value of the `scriptId` field
        var cfs = $.customFieldList.customField groupBy $.scriptId
        ---
        // Iterate over the custom fields (cfs) object
        cfs mapObject (v,k) -> (
            // The value is an array but I assume you will always have a single element in it.
            // Take the only element in the array (while removing the `scriptId` field) and test it
            (v[0] - "scriptId") match {
                // Check if the `o.value` is an Object if it is create a new object with the same key
                // while the value is a new object
                case o if (o.value is Object) -> {(k): { 
                    // add all fields from the object you are testing but the `internalId` and the `value`
                    (o -- ["internalId","value"]),
                    // add `internalId1` mapped to the top object's `internalId` field
                    internalId1: o.internalId,
                    // add `internalId2` mapped to the object stored in the `o.value.internalId`
                    internalId2: o.value.internalId,
                    // add all fields from the `o.value` but the `internalId`
                    (o.value - "internalId"),
                    // Finally just add `value: false to the new object
                    value: false    
                }}
                // When `o.value` is not an object then keep it as is.
                else -> {(k): $}
            }
        )
    })
}
0
votes

Here's another answer...Hope it helps

%dw 2.2
output application/json
var inpJson={
    "line": [{
            "lineNumber": 0,
            "credit": 17.98,
            "customFieldList": {
                "customField": [{
                        "internalId": "5780",
                        "scriptId": "custcol_del_externalid",
                        "value": "0000000111111"
                    },
                    {
                        "internalId": "1446",
                        "scriptId": "custcol_4601_witaxapplies",
                        "value": false
                    },
                    {
                        "internalId": "1837",
                        "scriptId": "custcol_nsts_gaw_col_approver",
                        "value": {
                            "internalId": "29540",
                            "name": "Alan Mcgee",
                            "typeId": "-4"
                        }
                    },
                    {
                        "internalId": "2648",
                        "scriptId": "custcol_foreign_expense",
                        "value": false
                    }
                ]

            }
        },
        {
            "lineNumber": 1,
            "debit": 17.98,
            "customFieldList": {
                "customField": [{
                        "internalId": "5780",
                        "scriptId": "custcol_del_externalid",
                        "value": "0000000111111"
                    },
                    {
                        "internalId": "1446",
                        "scriptId": "custcol_4601_witaxapplies",
                        "value": false
                    },
                    {
                        "internalId": "1837",
                        "scriptId": "custcol_nsts_gaw_col_approver",
                        "value": {
                            "internalId": "29540",
                            "name": "Joe Davis",
                            "typeId": "-4"
                        }
                    },
                    {
                        "internalId": "2648",
                        "scriptId": "custcol_foreign_expense",
                        "value": false
                    }
                ]

            }
        }

    ]
}
---
inpJson.line map(v1,k1) ->
{
    (v1 mapObject(v2,k2) ->
    {
        (k2):v2 
    } - "customFieldList"
    ),
    (v1.customFieldList.customField map (v3, k3) ->
                {
                    (v3.scriptId): {
                        value: v3.value,
                        internalId: v3.internalId
                    }
                }
        )   
}