Compare two JSON arrays using two or more columns values in Dataweave 2.0

Question

I had a task where I needed to compare and filter two JSON arrays based on the same values using one column of each array. So I used this answer of this question.

However, now I need to compare two JSON arrays matching two, or even three columns values.

I already tried to use one map inside other, however, it isn't working.

The examples could be the ones in the answer I used. Compare db.code = file.code, db.name = file.nm and db.id = file.identity

var db = [
    {
        "CODE": "A11",
        "NAME": "Alpha",
        "ID": "C10000"
    },
    {
        "CODE": "B12",
        "NAME": "Bravo",
        "ID": "B20000"
    },
    {
        "CODE": "C11",
        "NAME": "Charlie",
        "ID": "C30000"
    },
    {
        "CODE": "D12",
        "NAME": "Delta",
        "ID": "D40000"
    },
    {
        "CODE": "E12",
        "NAME": "Echo",
        "ID": "E50000"
    }
]  



var file = [
    {
        "IDENTITY": "D40000",
        "NM": "Delta",
        "CODE": "D12"
    },
    {
        "IDENTITY": "C30000",
        "NM": "Charlie",
        "CODE": "C11"
    }
]

Answer 1

See if this works for you

%dw 2.0
output application/json
var file = [
    {
        "IDENTITY": "D40000",
        "NM": "Delta",
        "CODE": "D12"
    },
    {
        "IDENTITY": "C30000",
        "NM": "Charlie",
        "CODE": "C11"
    }
]
var db = [
    {
        "CODE": "A11",
        "NAME": "Alpha",
        "ID": "C10000"
    },
    {
        "CODE": "B12",
        "NAME": "Bravo",
        "ID": "B20000"
    },
    {
        "CODE": "C11",
        "NAME": "Charlie",
        "ID": "C30000"
    },
    {
        "CODE": "D12",
        "NAME": "Delta",
        "ID": "D40000"
    },
    {
        "CODE": "E12",
        "NAME": "Echo",
        "ID": "E50000"
    }
]
---
file flatMap(v) -> ( 
    db filter (v.IDENTITY == $.ID and v.NM == $.NAME and v.CODE == $.CODE)
)

Using flatMap instead of map to flatten otherwise will get array of arrays in the output which is cleaner unless you are expecting a possibility of multiple matches per file entry, in which case I'd stick with map.