Need help in Dataweave Transformation

Question

I have the oracle database table CASH which contains below columns:

REGISTER    DATE    CASE    BAG TYPE
1234    24-SEP-18   1123    112 A
1234    24-SEP-18   1124    113 S
1234    24-SEP-18   1123    116 S
1234    24-SEP-18   1124    117 A
7895    24-SEP-18   2568    119 A
7895    24-SEP-18   2568    118 S

Where the register number are the cash registers which can have multiple CASE linked to it and each CASE can have more than one BAG and Type attached to it. I want to transform it into below XML in Dataweave:

<ROOT>
    <REGISTERS>
        <REGISTER>1234</REGISTER>
        <DATE>24-SEP-2018</DATE>
        <DETAILS>
            <BAG>1123</BAG>
            <DETAIl>
                <BAG>112</BAG>
                <TYPE>A</TYPE>
            </DETAIl>
            <DETAIl>
                <BAG>116</BAG>
                <TYPE>S</TYPE>
            </DETAIl>
        </DETAILS>
        <DETAILS>
            <BAG>1124</BAG>
            <DETAIl>
                <BAG>113</BAG>
                <TYPE>S</TYPE>
            </DETAIl>
            <DETAIl>
                <BAG>117</BAG>
                <TYPE>A</TYPE>
            </DETAIl>
        </DETAILS>
    </REGISTERS>
    <REGISTERS>
        <REGISTER>7895</REGISTER>
        <DATE>24-SEP-2018</DATE>
        <DETAILS>
            <BAG>2568</BAG>
            <DETAIl>
                <BAG>119</BAG>
                <TYPE>A</TYPE>
            </DETAIl>
            <DETAIl>
                <BAG>118</BAG>
                <TYPE>S</TYPE>
            </DETAIl>
        </DETAILS>
    </REGISTERS>
</ROOT>

Could you please give some pointers how can I achieve this in dataweave.

Thanks !!

Brad Cooper · Accepted Answer · 2018-10-08T00:29:26.180

1

Assuming you've already read the data from the database, you can use the following:

%dw 1.0
%output application/xml
---
ROOT: payload groupBy (($.REGISTER as :string) ++ ($.DATE as :string)) mapObject ((entries, number) -> {
    REGISTERS: {
        REGISTER: entries[0].REGISTER,
        DATE: entries[0].DATE as :string {format: "yyyy-MM-dd"},
        (entries groupBy $.CASE map DETAILS: {
            CASE: $.CASE[0],
            ($ map DETAIL: {
                BAG: $.BAG,
                TYPE: $.TYPE
            })
        })
    }
})

edited Oct 08 '18 at 00:29

answered Sep 27 '18 at 22:06

Brad Cooper

379
1
4

Thanks Brad for the help but the date can be different for the REGISTER(s). How can i groupBy based on store number and date combination. I tried using ++ operator but thats throwing an error. – user3368821 Sep 29 '18 at 10:51
@user3368821 - I've updated the answer. You can use '++' but you need to include the expression in brackets so that it's evaluated before the groupBy – Brad Cooper Oct 01 '18 at 21:07
Hi Brad, i did the same keeping it in the brackets but its throwing the error --> payload groupBy ($.REGISTER ++ $.DATE) mapObject ((entries, number) -> { ^ Cannot coerce a :object to a :string. – user3368821 Oct 02 '18 at 08:06
Can you provide more information on your flow? what steps do you do before this transformation? what type is the payload? etc. – Brad Cooper Oct 05 '18 at 04:17
Hi brad before this there is a database connector inside which there is a simple select query. Just after the database adapter transform is there where i am using the above snippet. Thanks – user3368821 Oct 05 '18 at 05:17
@user3368821, I see the issue now and I've updated the answer. I was reading the info you provided from a CSV file, hence everything was being treated as a string. When you read from the database the types will be determined by the DB column types - so there's every chance $.REGISTER is an Integer and $.DATE is a Date - hence the '++' operator will fail. It should work if you cast both to a string first. – Brad Cooper Oct 08 '18 at 00:31

Need help in Dataweave Transformation

1 Answers1