Struggling with a scenario in DataWeave

The below is the incoming sample payload. It has a field "Name" which can have any value eg, XYZ, PQR, ABC, and a specific value "TEST".

Incoming payload:

[
  {
    "Name": "XYZ",
    "Maths": "90",
    "English": "85"
  },
  {
    "Name": "ABC",
    "Maths" : "91",
    "English": "90"
  },
  {
    "Name": "TEST",
    "Maths" : "89",
    "English": "86"
  },
]

Below is a response from a database that has a list of values. The Incoming payload Names need to be compared with the "Name" field in the database objects.

Data from different application =

[ 
  {"Name": "ABC",
   "Gender": "Female",
    "Class": "4"},

  {"Name": "PQR",
   "Gender": "Male",
    "Class": "5"},

  {"Name": "XYZ",
   "Gender": "Female",
    "Class": "3"},
  
 {"Name": "OPQ",
    "Gender": "Female",
    "Class": "3"},
  ...
] 

If the "Name" in one or more of the objects in the Incoming payload is "TEST" then that object(s) needs to be skipped and the rest of the matching Names' objects should be the output; however, if the "Name" in any single object doesn't match with the list of Names in database response, then all the objects need to be skipped and an empty payload should be the output [].

In the above case, Names in incoming payload- XYZ, ABC are present in the database so below is the expected output(the TEST name object needs to be skipped):

 [
      {
        "Name": "XYZ",
        "Maths": "90",
        "English": "85"
      },
      {
        "Name": "ABC",
        "Maths" : "91",
        "English": "90"
      }
    ]

In the below scenario incoming payload has "Name" as "Stack" in one of the objects and "Stack" is not present in database response objects, so the empty payload output:

[
      {
        "Name": "XYZ",
        "Maths": "90",
        "English": "85"
      },
      {
        "Name": "ABC",
        "Maths" : "91",
        "English": "90"
      },
      {
        "Name": "Stack",   ----Not present in database response objects
        "Maths" : "91",
        "English": "90"
      }
    ]

Expected output: []

Solution 1:

Try with this approach:

Input

[
  {
    "Name": "XYZ",
    "Maths": "90",
    "English": "85"
  },
  {
    "Name": "ABC",
    "Maths" : "91",
    "English": "90"
  },
  {
    "Name": "TEST",
    "Maths" : "89",
    "English": "86"
  }
]

Script

%dw 2.0
output application/json
var compareWith = [ 
  {"Name": "ABC",
   "Gender": "Female",
    "Class": "4"},

  {"Name": "PQR",
   "Gender": "Male",
    "Class": "5"},

  {"Name": "XYZ",
   "Gender": "Female",
    "Class": "3"},
  
 {"Name": "OPQ",
    "Gender": "Female",
    "Class": "3"}
  
]

var inputNames = payload.Name

---

if (inputNames contains "TEST") payload filter(compareWith.Name contains $.Name) else if(sizeOf(payload filter(compareWith.Name contains $.Name)) != sizeOf(payload)) [] else payload

Output

[
  {
    "Name": "XYZ",
    "Maths": "90",
    "English": "85"
  },
  {
    "Name": "ABC",
    "Maths": "91",
    "English": "90"
  }
]