โ06-09-2023 02:17 AM
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
Solved! Go to Solution.
โ06-12-2023 01:23 AM
@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
โ06-09-2023 03:04 AM
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.
โ06-12-2023 01:03 AM
Aah yes , I see, I have found a way to add the rights fields to the array by using mapper expressions :).
โ06-12-2023 01:23 AM
@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