Join two arrays , one array with same unique ID

[JensDeveloper]

Hi,

So I am trying to join two arrays, but one array contains multiple unique IDs and the other one not.
Let me explain below:
One array from sql select with these objects:
{
“personid”: 433817,
“customernumber”: 10796670,
“firstname”: “Jens”,
“lastname”: “Lam”
}

The other sql select with an array of these object:
{
“personid”: 433817,
“customernumber”: 10796670,
“media”: “email”,
“mediaaddress”: “info@tesla.de”
},
{
“personid”: 433817,
“customernumber”: 10796670,
“media”: “phone”,
“mediaaddress”:“0484848484”
}

Now I want to join them together based on PersonID, but it the second select you have multiple records with same personID and I want it to look like this :
{
“personid”: 433817,
“customernumber”: 10796670,
“firstname”: “Jens”,
“lastname”: “Lam”
“media”: “email”,
“mediaaddress”: “info@tesla.de”
“media”: “phone”,
“mediaaddress”:“0484848484”
}

Anyone know an solution, I found one that I use union and then groupby the field personID but that makes it complicater to get all the fields later in different etls.

Regards

Jens

[Aleksandar_A]

Hi @JensDeveloper,

As I can see in the expected output you have an object with repeating keys, which is not possible in a JSON Object.

Let me know if this somehow helps.

BR,
Aleksandar.

[JensDeveloper]

Hi @AleksandarAngelevski ,

Aah yes , I see, I have found a way to add the rights fields to the array by using mapper expressions :).

[Abhishek_Soni37 · Accepted solution]

@JensDeveloper , It’s correct that we can’t have duplicate Keys in JSON object, You can use this mapper expression to achieve an array of contactInfo.

Test_Expression_2023_06_12.slp (4.3 KB)

I hope this helps. 🙂

Cheers