join data by position of different json elements on dataweave 2.0

Question

I have this JSON output of a SAP function:

{
    "Z_HYD_GET_INVOICES": {
        "import": {
            "BRANCH": "0142",
            "DOCNUMS": null,
            "FINAL_DATE": "Tue Oct 08 00:00:00 BST 2019",
            "INITIAL_DATE": "Mon Oct 07 00:00:00 BST 2019"
        },
        "export": {
            "ACCOUNTING": {
                "row": {
                    "DOCNUM": "0002990790",
                    "PSTDAT": "Mon Oct 07 00:00:00 BST 2019",
                    "BUKRS": "TRV"
                },
                "row": {
                    "DOCNUM": "0003006170",
                    "PSTDAT": "Mon Oct 07 00:00:00 BST 2019",
                    "BUKRS": "TRV"
                }
            },
            "FISCAL": {
                "row": {
                    "DOC_DOCNUM": "0002990790",
                    "DOC_NFTYPE": "ZW"
                },
                "row": {
                    "DOC_DOCNUM": "0003006170",
                    "DOC_INVTYPE": "ZW"
                }
            },
            "MESSAGE_RETURN": null,
            "STATUS_RETURN": null
        }
    }
}

And I want it to be like this:

{
  "invoices": [
    {
      "accounting": 
        {
          "accountingDocumentID": "0002990790",
          "taxEntryDate": "Mon Oct 07 00:00:00 BST 2019",
          "company": "TRV"
        },
      "fiscal": 
        {
          "fiscalDocument": {
            "fiscalDocumentID": "0002990790",
            "fiscalDocumentCategory": "ZW"
          }
        }
    },
    {
    "accounting": 
        {
          "accountingDocumentID": "0003006170",
          "taxEntryDate": "Mon Oct 07 00:00:00 BST 2019",
          "company": "TRV"
        },
    "fiscal": 
        {
          "fiscalDocument": {
            "fiscalDocumentID": "0003006170",
            "fiscalDocumentCategory": "ZW"
          }
        }
    }
  ]
}

I already tried some code with map and mapObject, but neither worked.

FISCAL.DOC_DOCNUM is equal to FISCAL.DOCNUM, but it could be better to if the transformation fits by position. I mean, join the first element of ACCOUNTING with the first one of FISCAL, and so on... If someone could also provide transform joining by ID, it'll be really nice, for future reference.

For joining items by ID there is the `join()` function and similar ones (`leftJoin`, `outerJoin` etc..). These are part of the core Arrays DW library. Keep in mind that to use these functions you must include `import * from dw::core::Arrays` in the script header. Ref: https://docs.mulesoft.com/mule-runtime/4.3/dw-arrays-functions-join — short stack stevens, Oct 17 '20 at 04:19

score 2 · Answer 1 · answered Oct 16 '20 at 19:42

Give this a shot.

%dw 2.0
output application/json
fun returnFiscalDoc (docnum) = {
    fiscalDocument : {(payload.Z_HYD_GET_INVOICES.export.FISCAL.*row filter ($.DOC_DOCNUM ~= docnum) map  {
        fiscalDocumentID: $.DOC_DOCNUM,
        fiscalDocumentCategory: $.DOC_NFTYPE default $.DOC_INVTYPE
    })}
}
---
invoices: payload.Z_HYD_GET_INVOICES.export.ACCOUNTING.*row map {
   accounting : 
     {
         accountingDocumentID: $.DOCNUM,
         taxEntryDate: $.PSTDAT,
         company:$.BUKRS
     },
   fiscal: returnFiscalDoc($.DOCNUM)
}

Though keep in mind your data under FISCA.row has DOC_NFTYPE and DOC_INVTYPE. Thus used default in the function above to get to the value of the required.

"FISCAL": {
                "row": {
                    "DOC_DOCNUM": "0002990790",
                    "DOC_NFTYPE": "ZW"
                },
                "row": {
                    "DOC_DOCNUM": "0003006170",
                    "DOC_INVTYPE": "ZW"
                }
            }

I forgot to change all keys of the example, sorry... but great solution, it helped me to understand what to do when this happens. Thanks! — mhery, Oct 16 '20 at 21:01

short stack stevens · Accepted Answer · 2020-10-17T04:38:52.763

Something like this should work

%dw 2.0
output application/json
var filteredExport = (payload.Z_HYD_GET_INVOICES."export") filterObject ($$ ~= "ACCOUNTING" or $$ ~= "FISCAL")
var filteredExportArray = filteredExport pluck $
var accounting = filteredExport.ACCOUNTING pluck $
var fiscal = filteredExport.FISCAL pluck $
---
{
    invoices: filteredExportArray map {
        accounting: {
            accountingDocumentID: accounting[$$].DOCNUM,
            taxEntryDate: accounting[$$].PSTDAT,
            company: accounting[$$].BUKRS
        },
        fiscal: {
            fiscalDocument: {
                fiscalDocumentID: fiscal[$$].DOC_DOCNUM,
                fiscalDocumentCategory: if(!isEmpty(fiscal[$$].DOC_NFTYPE)) fiscal[$$].DOC_NFTYPE else if(!isEmpty(fiscal[$$].DOC_INVTYPE)) fiscal[$$].DOC_INVTYPE else null
            }
        }
    }
}

You could also use a join function on the two arrays as you mentioned you'd like to see

%dw 2.0
import * from dw::core::Arrays
output application/json
var filteredExport = (payload.Z_HYD_GET_INVOICES."export") filterObject ($$ ~= "ACCOUNTING" or $$ ~= "FISCAL")
var accounting = filteredExport.ACCOUNTING pluck $
var fiscal = filteredExport.FISCAL pluck $
var joined = leftJoin(accounting, fiscal, (a) -> a.DOCNUM, (f) -> f.DOC_DOCNUM)
---
{
    invoices: joined map {
        accounting: {
            accountingDocumentID: $.l.DOCNUM,
            taxEntryDate: $.l.PSTDAT,
            company: $.l.BUKRS
        },
        fiscal: {
            fiscalDocument: {
                fiscalDocumentID: $.r.DOC_DOCNUM,
                fiscalDocumentCategory: $.r.DOC_NFTYPE
            }
        }
    }
}

I forgot to change all keys of the example. I think that your answer is more organized than the other one, so I chose it as the best. Thanks! — mhery, Oct 16 '20 at 21:02

join data by position of different json elements on dataweave 2.0

2 Answers2