join data by position of different json elements on dataweave 2.0

user_6621346 0
Viewed 251

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.

dataweave mule json
2 Answers

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"
                }
            }
user_30815780

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
            }
        }
    }
}
user_120423030
Related