CODEWITHSUNDEEP
filtermergemuledataweavemulesoft
mhery
mhery

Reputation: 2248

filter and merge two arrays with comparison based on two or more columns in dataweave 2.0

I have two big JSON arrays on which I have to filter and merge based on two columns:

mongo.NUM_CONTROL = mysql.DOCNUM
mongo.NUM_ITEM = mysql.LIN_NUM_ITEM

I already made this script on which I used map params (item and key) to do not have to map each column of each json (they have about 50 columns each)

Also, it works for comparing NUM_CONTROL with DOCNUM.

However, I couldn't make it work for NUM_ITEM and LIN_NUM_ITEM.

mongo map (mItems, mKeys) -> using (mongoCtrl = mItems.NUM_CONTROL)
{
    (mysql filter ($.*DOCNUM contains mongoCtrl) map (sItems, sKeys) -> {
        (sItems mapObject (sItem, sKey) ->
            (sKey): sItem
        ),
        (mItems mapObject (mItem, mKey) ->
            (mKey): mItem
        )
    })  
}

EDIT: here are some example data

mongo = 
[
    {
        "VLR_UNIT": 1581.1,
        "OBSERVACAO": null,
        "COD_NATUREZA_OP": "155",
        "VLR_TRIBUTO_ICMS": 1715.81,
        "ALIQ_ICMS_ORIGEM": 7,
        "VLR_BASE_ISENTA_IPI": 7,
        "COD_CFO": "5171",
        "VLR_TRIBUTO_ICMMST": 7,
        "VLR_BASE_REDUZIDA_IPI": 7,
        "DATA_FISCAL": "1718-78-73T77:77:77",
        "COD_EMPRESA": "TRV",
        "VLR_BASE_TRIBUTADA_ICMSST": 7,
        "VLR_BASE_ISENTA_ICMSST": 7,
        "COD_FEDERAL": "77753",
        "VLR_BASE_OUTRAS_IPI": 57637.4,
        "VLR_BASE_ISENTA_ICMS": 7,
        "VLR_DESONERADO": 7,
        "COD_PRODUTO": "1777776316",
        "RAIZ": "77787381",
        "VLR_ICMSS_N_ESCRIT": 7,
        "IND_SIMPLES_NAC": "S",
        "VLR_ITEM": 57637.4,
        "ALIQ_TRIBUTO_ICMS": 18,
        "QUANTIDADE": 31,
        "DATA_EMISSAO": "1718-78-73T77:77:77",
        "ALIQ_ICMS_DEST": 18,
        "VLR_BASE_OUTRAS_ICMS": 7,
        "NUM_DOCFIS": "777717117",
        "VLR_BASE_REDUZIDA_ICMS": 38375.16,
        "VLR_DESCONTO": 7,
        "VLR_CONTAB_ITEM": 57637.4,
        "VLR_BASE_TRIBUTADA_IPI": 7,
        "CLAS_ITEM": 4,
        "IND_PRODUTO": "1",
        "INSC_SUFRAMA": null,
        "VLR_IPI_NDESTAC": 7,
        "VLR_DIFAL": 7,
        "COD_MEDIDA": "TO",
        "VLR_FRETE": 7,
        "VLR_OUTRAS": 7,
        "COD_SITUACAO_B": "17",
        "COD_SITUACAO_A": "7",
        "VLR_TOT_NOTA": 57637.4,
        "MOVTO_E_S": "8",
        "COD_ESTAB": "7141",
        "NUM_ITEM": 1,
        "VLR_SEGURO": 7,
        "COD_MODELO": "55",
        "COD_NBM": "31758787",
        "COD_MUNICIPIO_ORIG": 6571,
        "VLR_ALIQ_ICMSST": 7,
        "SITUACAO_DOCUMENTO": "NORMAL",
        "VLR_BASE_ICMSS_N_ESCRIT": 7,
        "VLR_TRIBUTO_IPI": 7,
        "NUM_CONTROL": "7771838361",
        "VLR_BASE_OUTRAS_ICMSST": 7,
        "VLR_BASE_TRIBUTADA_ICMS": 11155.14,
        "UF_PLANTA": "BA",
        "VLR_BASE_REDUZIDA_ICMSST": 7,
        "ALIQ_TRIBUTO_IPI": 7,
        "COD_MUNICIPIO_DEST": 31357,
        "SERIE_DOCFIS": "57",
        "DIF_ALIQ_TRIBUTO": 7,
        "VLR_ICMS_NDESTAC": 7
    },
    {
        "VLR_UNIT": 1357,
        "OBSERVACAO": null,
        "COD_NATUREZA_OP": "155",
        "VLR_TRIBUTO_ICMS": 1316.46,
        "ALIQ_ICMS_ORIGEM": 7,
        "VLR_BASE_ISENTA_IPI": 7,
        "COD_CFO": "5171",
        "VLR_TRIBUTO_ICMMST": 7,
        "VLR_BASE_REDUZIDA_IPI": 7,
        "DATA_FISCAL": "1718-78-73T77:77:77",
        "COD_EMPRESA": "TRV",
        "VLR_BASE_TRIBUTADA_ICMSST": 7,
        "VLR_BASE_ISENTA_ICMSST": 7,
        "COD_FEDERAL": "77753",
        "VLR_BASE_OUTRAS_IPI": 31877,
        "VLR_BASE_ISENTA_ICMS": 7,
        "VLR_DESONERADO": 7,
        "COD_PRODUTO": "1777776833",
        "RAIZ": "78714167",
        "VLR_ICMSS_N_ESCRIT": 7,
        "IND_SIMPLES_NAC": "S",
        "VLR_ITEM": 31877,
        "ALIQ_TRIBUTO_ICMS": 18,
        "QUANTIDADE": 14,
        "DATA_EMISSAO": "1718-78-73T77:77:77",
        "ALIQ_ICMS_DEST": 18,
        "VLR_BASE_OUTRAS_ICMS": 7,
        "NUM_DOCFIS": "777717114",
        "VLR_BASE_REDUZIDA_ICMS": 15586.33,
        "VLR_DESCONTO": 7,
        "VLR_CONTAB_ITEM": 31877,
        "VLR_BASE_TRIBUTADA_IPI": 7,
        "CLAS_ITEM": 4,
        "IND_PRODUTO": "1",
        "INSC_SUFRAMA": null,
        "VLR_IPI_NDESTAC": 7,
        "VLR_DIFAL": 7,
        "COD_MEDIDA": "TO",
        "VLR_FRETE": 7,
        "VLR_OUTRAS": 7,
        "COD_SITUACAO_B": "17",
        "COD_SITUACAO_A": "7",
        "VLR_TOT_NOTA": 31877,
        "MOVTO_E_S": "8",
        "COD_ESTAB": "7141",
        "NUM_ITEM": 1,
        "VLR_SEGURO": 7,
        "COD_MODELO": "55",
        "COD_NBM": "31754777",
        "COD_MUNICIPIO_ORIG": 6571,
        "VLR_ALIQ_ICMSST": 7,
        "SITUACAO_DOCUMENTO": "NORMAL",
        "VLR_BASE_ICMSS_N_ESCRIT": 7,
        "VLR_TRIBUTO_IPI": 7,
        "NUM_CONTROL": "7771847474",
        "VLR_BASE_OUTRAS_ICMSST": 7,
        "VLR_BASE_TRIBUTADA_ICMS": 7313.67,
        "UF_PLANTA": "BA",
        "VLR_BASE_REDUZIDA_ICMSST": 7,
        "ALIQ_TRIBUTO_IPI": 7,
        "COD_MUNICIPIO_DEST": 33377,
        "SERIE_DOCFIS": "57",
        "DIF_ALIQ_TRIBUTO": 7,
        "VLR_ICMS_NDESTAC": 7
    }
]

and

mysql = 
[
    {
        "DOCNUM": "7771838361",
        "NFTYPE": "Z1",
        "DOCTYP": "1",
        "DIRECT": "1",
        "DOCDAT": "Tue Sep 73 77:77:77 BST 1718",
        "PSTDAT": "Tue Sep 73 77:77:77 BST 1718",
        "MODEL": "55",
        "SERIES": "57",
        "SUBSER": null,
        "CRENAM": "A837335",
        "MANUAL": null,
        "BELNR": null,
        "GJAHR": "7777",
        "BUKRS": "TRV",
        "BRANCH": "7141",
        "PARID": "7717781611",
        "PARVW": "WE",
        "PARTYP": "C",
        "CANCEL": null,
        "COD_SIT": "77",
        "CANDAT": null,
        "NFENUM": "777717117",
        "CRT_BUPLA": "3",
        "CRT_PARTNER": null,
        "DOCREF": "7777777777",
        "NFTOT": "57637.47",
        "OBSERVAT": null,
        "MODFRETE": "1",
        "LIN_ITMNUM": "777747",
        "LIN_NUM_ITEM": "771",
        "LIN_MATNR": "777777771777776316",
        "LIN_MATKL": "57",
        "LIN_CFOP": "5171AA",
        "LIN_NBM": "3175.87.87",
        "LIN_MATORG": "7",
        "LIN_TAXSIT": "1",
        "LIN_TAXSIT1": "77753",
        "LIN_MWSKZ": null,
        "LIN_OWNPRO": "X",
        "LIN_MATUSE": "1",
        "LIN_NETWR": "57637.47",
        "LIN_TAXLW1": "B71",
        "LIN_TAXLW1": "53A",
        "LIN_NDUS3": "77",
        "LIN_WERKS": "SSA3",
        "LIN_NFNET": "57637.47",
        "LIN_NETWRT": "57637.47",
        "LIN_NFNETT": "57637.47",
        "LIN_TAXLW4": "76A",
        "LIN_TAXSI4": "76",
        "LIN_TAXLW5": "76A",
        "LIN_TAXSI5": "76",
        "LIN_XPED": "77871873",
        "LIN_NITEMPED": null,
        "LIN_INDINCENTIVO": "1",
        "LIN_REFKEY": "7787854684",
        "LIN_REFITM": "777747",
        "KNA1_KUNNR": "7717781611",
        "KNA1_REGIO": "BA",
        "KNA1_BRSCH": "7771",
        "KNA1_CRTN": null,
        "KNA1_STCD1": "77787381777177",
        "KNA1_STCD1": null,
        "KNA1_ICMSTAXPAY": "Z4",
        "LFA1_LIFNR": null,
        "LFA1_BRSCH": null,
        "LFA1_CRTN": null,
        "LFA1_ICMSTAXPAY": null,
        "LFA1_STCD1": null,
        "LFA1_STCD1": null,
        "LFA1_KTOKK": null,
        "MARA1_BEHVO": null,
        "MARA1_MTART": null,
        "MARA1_TRAGR": null,
        "MARA_BEHVO": "74",
        "MARA_MTART": "FERT",
        "MARA_TRAGR": "7771",
        "MARA_EXTWG": "71",
        "BKPF_AWKEY": "7787854684",
        "BKPF_BUKRS": "TRV",
        "BKPF_BELNR": "7787186147",
        "BKPF_GJAHR": "1718",
        "J_1BTICMSTAXPAYT_ICMSTAXPAY": null,
        "TWEWT_EXTWG": "71",
        "TWEWT_EWBEZ": "Crop Nutrition 37",
        "T777Y_KTOKK": null,
        "T777Y_TXT37": null,
        "VTTK_TKNUM": null,
        "VTTK_SHTYP": null,
        "TVTKT_BEZEI": "OUT Direct",
        "VTTP_VBELN": "7787885763",
        "LIPS_VBELN": "7787885763",
        "LIPS_MATNR": "777777771777775883",
        "T144T_BEHVO": "74",
        "VLR_BASE_TRIB_ICMS": "11155.14",
        "VLR_BASE_ISENTA_ICMS": "38375.16",
        "VLR_BASE_OUTRA_ICMS": "7.77",
        "VLR_BASE_RED_ICMS": "11.13",
        "ALIQ_TRIBUTO_ICMS": "18.77",
        "VLR_TRIBUTO_ICMS": "1715.81",
        "VLR_ICMS_NDESTAC": "7.77",
        "VLR_BASE_TRIB_ICMSST": "7.77",
        "VLR_TRIBUTO_ICMSST": "7.77",
        "ALIQ_TRIBUTO_DIFAL": "7.77",
        "VLR_DIFAL": "7.77",
        "VLR_BASE_TRIB_IPI": "7.77",
        "VLR_BASE_ISENTA_IPI": "7.77",
        "VLR_BASE_OUTRA_IPI": "57637.47",
        "VLR_BASE_RED_IPI": "7.77",
        "ALIQ_TRIBUTO_IPI": "7.77",
        "VLR_TRIBUTO_IPI": "7.77",
        "VLR_BASE_TRIB_PIS": "57637.47",
        "ALIQ_TRIBUTO_PIS": "7.77",
        "VLR_TRIBUTO_PIS": "7.77",
        "VLR_BASE_TRIB_COFINS": "57637.47",
        "ALIQ_TRIBUTO_COFINS": "7.77",
        "VLR_TRIBUTO_COFINS": "7.77"
    },
    {
        "DOCNUM": "7771847474",
        "NFTYPE": "Z1",
        "DOCTYP": "1",
        "DIRECT": "1",
        "DOCDAT": "Tue Sep 73 77:77:77 BST 1718",
        "PSTDAT": "Tue Sep 73 77:77:77 BST 1718",
        "MODEL": "55",
        "SERIES": "57",
        "SUBSER": null,
        "CRENAM": "A817347",
        "MANUAL": null,
        "BELNR": null,
        "GJAHR": "7777",
        "BUKRS": "TRV",
        "BRANCH": "7141",
        "PARID": "7717771413",
        "PARVW": "WE",
        "PARTYP": "C",
        "CANCEL": null,
        "COD_SIT": "77",
        "CANDAT": null,
        "NFENUM": "777717114",
        "CRT_BUPLA": "3",
        "CRT_PARTNER": null,
        "DOCREF": "7777777777",
        "NFTOT": "31877.77",
        "OBSERVAT": null,
        "MODFRETE": "7",
        "LIN_ITMNUM": "777717",
        "LIN_NUM_ITEM": "771",
        "LIN_MATNR": "777777771777776833",
        "LIN_MATKL": "15",
        "LIN_CFOP": "5171AA",
        "LIN_NBM": "3175.47.77",
        "LIN_MATORG": "7",
        "LIN_TAXSIT": "1",
        "LIN_TAXSIT1": "77753",
        "LIN_MWSKZ": null,
        "LIN_OWNPRO": "X",
        "LIN_MATUSE": "1",
        "LIN_NETWR": "31877.77",
        "LIN_TAXLW1": "B71",
        "LIN_TAXLW1": "53A",
        "LIN_NDUS3": "77",
        "LIN_WERKS": "SSA3",
        "LIN_NFNET": "31877.77",
        "LIN_NETWRT": "31877.77",
        "LIN_NFNETT": "31877.77",
        "LIN_TAXLW4": "76A",
        "LIN_TAXSI4": "76",
        "LIN_TAXLW5": "76A",
        "LIN_TAXSI5": "76",
        "LIN_XPED": "77814838",
        "LIN_NITEMPED": null,
        "LIN_INDINCENTIVO": "1",
        "LIN_REFKEY": "7787855161",
        "LIN_REFITM": "777717",
        "KNA1_KUNNR": null,
        "KNA1_REGIO": null,
        "KNA1_BRSCH": null,
        "KNA1_CRTN": null,
        "KNA1_STCD1": null,
        "KNA1_STCD1": null,
        "KNA1_ICMSTAXPAY": null,
        "LFA1_LIFNR": null,
        "LFA1_BRSCH": "7771",
        "LFA1_CRTN": null,
        "LFA1_ICMSTAXPAY": null,
        "LFA1_STCD1": "73741845777118",
        "LFA1_STCD1": null,
        "LFA1_KTOKK": "FPJU",
        "MARA1_BEHVO": null,
        "MARA1_MTART": null,
        "MARA1_TRAGR": null,
        "MARA_BEHVO": "74",
        "MARA_MTART": "FERT",
        "MARA_TRAGR": "7771",
        "MARA_EXTWG": "71",
        "BKPF_AWKEY": "7787855161",
        "BKPF_BUKRS": "TRV",
        "BKPF_BELNR": "7787186488",
        "BKPF_GJAHR": "1718",
        "J_1BTICMSTAXPAYT_ICMSTAXPAY": null,
        "TWEWT_EXTWG": "71",
        "TWEWT_EWBEZ": "Crop Nutrition 37",
        "T777Y_KTOKK": "FPJU",
        "T777Y_TXT37": "PJ Vendors",
        "VTTK_TKNUM": null,
        "VTTK_SHTYP": null,
        "TVTKT_BEZEI": "OUT Direct",
        "VTTP_VBELN": "7787885763",
        "LIPS_VBELN": "7787885763",
        "LIPS_MATNR": "777777771777775883",
        "T144T_BEHVO": "74",
        "VLR_BASE_TRIB_ICMS": "7313.67",
        "VLR_BASE_ISENTA_ICMS": "15586.33",
        "VLR_BASE_OUTRA_ICMS": "7.77",
        "VLR_BASE_RED_ICMS": "11.13",
        "ALIQ_TRIBUTO_ICMS": "18.77",
        "VLR_TRIBUTO_ICMS": "1316.46",
        "VLR_ICMS_NDESTAC": "7.77",
        "VLR_BASE_TRIB_ICMSST": "7.77",
        "VLR_TRIBUTO_ICMSST": "7.77",
        "ALIQ_TRIBUTO_DIFAL": "7.77",
        "VLR_DIFAL": "7.77",
        "VLR_BASE_TRIB_IPI": "7.77",
        "VLR_BASE_ISENTA_IPI": "7.77",
        "VLR_BASE_OUTRA_IPI": "31877.77",
        "VLR_BASE_RED_IPI": "7.77",
        "ALIQ_TRIBUTO_IPI": "7.77",
        "VLR_TRIBUTO_IPI": "7.77",
        "VLR_BASE_TRIB_PIS": "31877.77",
        "ALIQ_TRIBUTO_PIS": "7.77",
        "VLR_TRIBUTO_PIS": "7.77",
        "VLR_BASE_TRIB_COFINS": "31877.77",
        "ALIQ_TRIBUTO_COFINS": "7.77",
        "VLR_TRIBUTO_COFINS": "7.77"
    }
]

The expected output must have these two JSON arrays merged based on previous conditions

Upvotes: 0

Views: 523

Answers (2)

olamiral
olamiral

Reputation: 1296

Use the following dataweave expression:

%dw 2.0
output application/json
---
flatten(mongo map (mItem, mKey) -> 
    (mysql filter ($.DOCNUM ==  mItem.NUM_CONTROL) and ($.LIN_NUM_ITEM as Number == mItem.NUM_ITEM)) map (sItem, sKey) -> 
        mItem ++ sItem)

Upvotes: 2

Salim Khan
Salim Khan

Reputation: 4303

Try with this.

mongo map (mItems, mKeys) ->
{
    (mysql filter (($.DOCNUM ==  mItems.NUM_CONTROL) and  ($.LIN_NUM_ITEM as Number == mItems.NUM_ITEM)) map (sItems, sKeys) -> {
        (sItems mapObject (sItem, sKey) ->
            (sKey): sItem
        ),
        (mItems mapObject (mItem, mKey) ->
            (mKey): mItem
        )
    })  
}

Upvotes: 3

Related Questions