Forum Discussion

JensDeveloper's avatar
JensDeveloper
Contributor II
3 years ago
Solved

Join two arrays , one array with same unique ID

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

3 Replies

  • Igor_Bozhinovsk's avatar
    Igor_Bozhinovsk
    New Contributor III
    3 years ago

    Thanks Alchemiz , the array is now spliited in two. One array with 100 elements and the other with the remaining elements.

    This is the current output :

    "ShipmentCreateDate": "01/01/2023",
"ShipmentID": "1",
"SerialNumbers": [
  [
    {
      "SerialNo": "123"
    },
	{ "SerialNo":"456"
    }
  ],
  [
	{
	  "SerialNo": "101"
	},
	{
	  "SerialNo": "102"
	}
  ]
 ]

    I was wondering if something like this output is possible :

    "ShipmentCreateDate": "01/01/2023",
"ShipmentID": "1",
"SerialNumbers": [
  [
    {
      "SerialNo": "123"
    },
	{ 
	  "SerialNo":"456"
    }
  ]
"ShipmentCreateDate": "01/01/2023",
"ShipmentID": "1",
"SerialNumbers": [
  [
	{
	  "SerialNo": "101"
	},
	{
	  "SerialNo": "102"
	}
  ]
 ]

    Where the first output would be the array with the 100 elements and the remaining fields that are not part of the array and the second output would be the array with the remaining elements and the fields that are not part of the array.

    Regards,
    Igor