Join array of Objects

[sravan]

Hi ,

I have a requirement to join the array1 with array2 in the same document and fetch one column from array 2 into array1 if condition matches. I can do it by splitting the arrays and join them back again. I would like to to know if i can do it in minimal way using expression language? please help 

studySiteId from array1 should match with studySiteId in array2 and get studySiteReferenceValue from array2 and put it in array1 with key value pair. if not matches put studySiteReferenceValue as null

INPUT 

[
{
"Array1" : [
{
"Study": "D123",
"studySiteNumber":"123",
"studySiteId":806,
"countryCode":"BR"
},
{
"Study": "D124",
"studySiteNumber":"0820",
"studySiteId":807,
"countryCode":"BR"
}
],
"Array2" : [
{
"isRefFound":true,
"studySiteId":806,
"studySiteReferenceTypeId":100,
"studySiteReferenceValue":"0SI000000006E06",
"sourceSystemId":22,
"etlIsDeletedFlg":false

},
{
"isRefFound":true,
"studySiteId":8088,
"studySiteReferenceTypeId":100,
"studySiteReferenceValue":"0SI000000006E06",
"sourceSystemId":22,
"etlIsDeletedFlg":false

}

]
}
]





OUTPUT:



[
{
"Array1" : [
{
"Study": "D123",
"studySiteNumber":"123",
"studySiteId":806,
"countryCode":"BR",
"studySiteReferenceValue":"0SI000000006E06"
},
{
"Study": "D124",
"studySiteNumber":"0820",
"studySiteId":807,
"countryCode":"BR",
"studySiteReferenceValue":""
}
],
"Array2" : [
{
"isRefFound":true,
"studySiteId":806,
"studySiteReferenceTypeId":100,
"studySiteReferenceValue":"0SI000000006E06",
"sourceSystemId":22,
"etlIsDeletedFlg":false

},
{
"isRefFound":true,
"studySiteId":8088,
"studySiteReferenceTypeId":100,
"studySiteReferenceValue":"0SI000000006E06",
"sourceSystemId":22,
"etlIsDeletedFlg":false

}

]
}
]

[koryknick]

@sravan - this is a great use case for Array Functions and Object Functions in SnapLogic expressions!  Use a Mapper snap with both "Null-safe access" and "Pass through" enabled, then create a mapping expression as follows:
Expression: 

$Array1.map(a1=> a1.extend(
{ studySiteReferenceValue : $Array2.find(a2=> a2.studySiteId == a1.studySiteId).studySiteReferenceValue } 
)
)

Target Path:

$Array1

Let's break down that expression just a bit.

$Array1.map(a1=> a1.extend(

Here we are taking the $Array1 and will iterate through each element using the map() method.  "a1=>" is the name we will use to refer to each element of the array and the "=>" is how an inline (or callback) function is indicated.  So "a1.extend" is referring to the array element we're working on, and the extend() method allows us to merge an object into the current object (remember a1 is an object in your array).

{ studySiteReferenceValue : $Array2.find(a2=> a2.studySiteId == a1.studySiteId).studySiteReferenceValue } 

So here is the new object that we are adding into each array element.  "studySiteReferenceValue" is the new field name you wanted, and the value is looking into $Array2 and using the find() method to match the array elements on studySiteId.  Once the value is found, we're grabbing the "studySiteReferenceValue" from the found object in Array2.  Note that if you don't use "Null safe access" on the Mapper, this will fail because you can't reference a field in null, which is what is returned if the find() method doesn't identify a matching element from the array.

Hope this helps!