Combine JSON arrays using a common key

How To
,
1

Dear Community,

Me again.

Here is my scenario: I have a first JSON array looking like hereunder:

[ {“Key”: “Value1”, “Field1”: “Value2”, “Field2”: “Value3”},
{“Key”: “Value2”, “Field1”: “Value4”, “Field2”: “Value5”} ]

I have a second JSON array looking like so:

[ {“Key”: “Value1”, “Field3”: “Value6”, “Field4”: “Value7”},
{“Key”: “Value2”, “Field3”: “Value8”, “Field4”: “Value9”} ]

Is there any way I can combine those without going with any iterator in order to get the following result?

[ {“Key”: “Value1”, “Field1”: “Value2”, “Field2”: “Value3”, “Field3”: “Value6”, “Field4”: “Value7”},
{“Key”: “Value2”, “Field1”: “Value4”, “Fileld2”: “Value5”, “Field3”: “Value8”, “Field4”: “Value9”} ]

Both arrays have common keys and I have an exact 1 to 1 relationship in between both arrays. I’d like to aggregate both arrays into one with all fields having the same key in the same string, preceded by the said key.

Hope this is feasible…going the array aggregator then iterator with get() and map() functions way did not make it performance wise.

Thanks to all for your support!

Tim

2

Hello @Timbleking,

Is this different than your other question?

3

Hi Don,

Related but this solution does not fit the need performance-wise. Reason why I try to find a better solution, even if technically, it works.

4

Got it sorted on my own, using aggregators and 1 iterator completely differently (and finally get() and map() functions but there as well a little bit differently).

I guess this is totally dependent on my scenario so won’t develop here, unless someone would be interesting.

Alternatively, it could be interesting to know how you experts would sort the following out: instead of merging two arrays (which will basically add rows), how to merge columns from two different arrays?

Array 1: column1, column2, column3

Array 2: column4, column5

Resulting array: column1, column2, column3, column4, column5

2 Likes
5

Glad you got it sorted!

It’s possible this method works best for you, but any detail you’re able to provide may be very helpful for those who might stumble upon this post in the future!

6

Alright, so there it is.

First of all, be aware that I use the Make platform thru Celonis, for those of you know.

I have some flows in my ERP that have a part of local execution and another part in central execution. But both parts of the flows are actually at the same level in the data model in Celonis, with a non-natural link in between the documents.

So I have to first query data for the local flows with some parameters, then query the central part with some other paramters, then I have to match both parts with the unique common key.

From there, I just wanted to merge columns from query two with columns of query one, as mentioned in my previous answer. I did not find a simple way to do it thru any Celonis module nor using any kind of array, JSON or others.

So what I did is using an array aggregator for the first query, then the same for the second query, and use a Set Multi Variables module to get native fields from query 1 (directly from its array format) and get fields from query 2 (directly from its array format as well) using the common key.

It translates somehow like this:

get (map (Central Data Array; Central Document Number; Common Key; Common Key Value from Local Data Array ) ; 1 )

Sorry, my job is quite confidential so cannot add that much of detail. Hope this is enough to understand it.

And finally, the complete workflow looks like this:

image
image2671×137 54.7 KB

Step 1: Get Local Data
Step 2: Aggregate in Local Data Array
Step 3: Build complete common key
Step 4: Get Central Data
Step 5: Aggregate in Central Data Array
Step 6: Iterate Local Data Array
Step 7: Map Local and Central Data Array fields using get and map
Step 8: Aggregate in a consolidated array to build Advanced CSV
Step 9: Build Advanced CSV
Step 10 to 13: Complete data with additional scenario and merge Advanced CSVs
Step 14: Format CSV to be sent to Outlook module
Step 15: Send CSV thru Outlook message

Hope this helps. And still, if anyone knows how to merge columns from two arrays or queries, more than welcome to elaborate.