How to get last matching record after the left join in Mulesoft

Question

We have to get the all records from Array1 and only last matching records from Array2. I tried the leftJoin in dataweave but it's matching all records in Array2.

Array1:

    [
        { 
            "name": "Abc", 
            "address":"Mumbai",
            "phone":"91-1234567891",
            "id":123
        }, 
        { 
            "name": "Pqr", 
            "address":"Pune",
            "phone":"91-1234985438",
            "id":456
        }, 
        { 
            "name": "Abc",
            "address":"Delhi",
            "phone":"91-1234567891",
            "id":123
        }
    ]

Array2:

[
    { 
        "sid":123,
        "postalCode": "67890",
        "country": "USA"
    }, 
    { 
        "sid":456,
        "postalCode": "54983",
        "country": "UK"
    }, 
    { 
        "sid":123,
        "postalCode": "27932",
        "country": "EU"
    }
]

Here id=123 is matching with 2 records in Array2 but we have to take the last matching record.

Expected Output:

[
  {
    "name": "Abc",
    "address": "Mumbai",
    "phone": "91-1234567891",
    "id": 123,
    "sid": 123,
    "postalCode": "27932",
    "country": "EU"
  },
  {
    "name": "Pqr",
    "address": "Pune",
    "phone": "91-1234985438",
    "id": 456,
    "sid": 456,
    "postalCode": "54983",
    "country": "UK"
  },
  {
    "name": "Abc",
    "address": "Delhi",
    "phone": "91-1234567891",
    "id": 123,
    "sid": 123,
    "postalCode": "27932",
    "country": "EU"
  }
]

Output should have the same num of records that are there in Array1 (which is 3 records) and match with last matching record from Array2 even if there are 2 occurrences.

I tried the leftJoin below but I'm getting more than 3 matching records in output.

%dw 2.0
output application/json
import * from dw::core::Arrays
---
(leftJoin(Array1, Array2, (Array1) -> Array1.id, (Array2) -> Array2.sid)) map {
    "name": $.l.name, 
    "address": $.l.address,
    "phone": $.l.phone,
    "id": $.l.id,
    "sid": $.r.sid,
    "postalCode": $.r.postalCode,
    "country": $.r.country
}

Answer 1

The problems are that there are duplicated elements in Array2 with the same sid and you want to use the last possible element of Array2 if there are more than one option. Your script is not doing anything in that regard. To avoid those issues I first reverse the order of Array2, so the last element is first, then use distinctBy() to remove duplicates. I just use the newly ordered and unique array instead.

%dw 2.0
output application/json
import * from dw::core::Arrays

var Array1=[
        { 
            "name": "Abc", 
            "address":"Mumbai",
            "phone":"91-1234567891",
            "id":123
        }, 
        { 
            "name": "Pqr", 
            "address":"Pune",
            "phone":"91-1234985438",
            "id":456
        }, 
        { 
            "name": "Abc",
            "address":"Delhi",
            "phone":"91-1234567891",
            "id":123
        }
    ]
var Array2=[
  {
    "name": "Abc",
    "address": "Mumbai",
    "phone": "91-1234567891",
    "id": 123,
    "sid": 123,
    "postalCode": "27932",
    "country": "EU"
  },
  {
    "name": "Pqr",
    "address": "Pune",
    "phone": "91-1234985438",
    "id": 456,
    "sid": 456,
    "postalCode": "54983",
    "country": "UK"
  },
  {
    "name": "Abc",
    "address": "Delhi",
    "phone": "91-1234567891",
    "id": 123,
    "sid": 123,
    "postalCode": "27932",
    "country": "EU"
  }
]

var Array2reversed=Array2[-1 to 0] distinctBy ((item, index) -> item.sid)

---
(leftJoin(Array1, Array2reversed, (Array1) -> Array1.id, (Array2) -> Array2.sid)) 
    map {
        "name": $.l.name, 
        "address": $.l.address,
        "phone": $.l.phone,
        "id": $.l.id,
        "sid": $.r.sid,
        "postalCode": $.r.postalCode,
        "country": $.r.country
    }

Output:

[
  {
    "name": "Abc",
    "address": "Mumbai",
    "phone": "91-1234567891",
    "id": 123,
    "sid": 123,
    "postalCode": "27932",
    "country": "EU"
  },
  {
    "name": "Pqr",
    "address": "Pune",
    "phone": "91-1234985438",
    "id": 456,
    "sid": 456,
    "postalCode": "54983",
    "country": "UK"
  },
  {
    "name": "Abc",
    "address": "Delhi",
    "phone": "91-1234567891",
    "id": 123,
    "sid": 123,
    "postalCode": "27932",
    "country": "EU"
  }
]