基于dataweave 2.0中两个或多个列的比较过滤和合并两个数组 [英] filter and merge two arrays with comparison based on two or more columns in dataweave 2.0
本文介绍了基于dataweave 2.0中两个或多个列的比较过滤和合并两个数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个大的 JSON 数组，必须对它们进行 filter 和 merge 基于两列：
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
我已经制作了此脚本，并在其中使用了 map 参数（ code> item 和 key ）来不必映射每个json的每一列（它们每个约有50列）
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)
此外，它还可以将NUM_CONTROL与DOCNUM进行比较。
Also, it works for comparing NUM_CONTROL with DOCNUM.
但是，我无法将其用于NUM_ITEM和LIN_NUM_ITEM。
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
)
})
}
编辑：这是一些示例数据
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
}
]
和
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"
}
]
预期输出必须具有基于先前条件合并的这两个JSON数组
The expected output must have these two JSON arrays merged based on previous conditions
推荐答案
使用以下数据编织表达式：
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)
这篇关于基于dataweave 2.0中两个或多个列的比较过滤和合并两个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
