# How to merge data of two arrays by key "month" (mapping) into 1 array and add calculations?

**URL:** <https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382>\
**Category:** General\
**Created:** [5 November 2020 16:40 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382 "2020-11-05T16:40:09Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [5 November 2020 16:40 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/1 "2020-11-05T16:40:09Z")

</div>

Hi,

I try to merge data from 2 arrays into 1 based on the same date (or key).

[USE-CASE]:  
_net power usage_  
I want to calculate my net powerusage by subtracting (eliminating) my kW used by my electric car charger to analyse my household usage.

[PROBLEM]:  
I can merge the 2 queries into 1 array by differentating the msg.topic by using a JOIN node. But this doesn't merge the data on the same key (no mapping) but extends the both input into 1 larger object with more object within (seems logical that this is normal behaviour).

[PREFERED SOLUTION]:  
1 Array with both values (greenchoice + newmotion) + 1 calculation (KwNet = Kw greenchoice - Kw newmotion) sorted by Month with max 12 objects (full year).

[DATA]:  
I have allready two queries ready with simular output:

_Query 1 [array (key/value)]_: Greenchoice (my power supplier) in kWh:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9fa5c05ff11e89509180676d797a49e45778317.png)

_Query 2 [array (key/value)]_: New Motion (my car charger) in kWh:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/4/14a56b6e1489093f2ed191afaf09a8ecc79bfa26.png)

JOIN RESULTS (by msg.topic):  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fb9182ca379ea049b5d8dcb2db5e48db8af0c22c.png)  
21 (total of) objects instead of the max 12 (months in a year)

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [5 November 2020 17:20 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/2 "2020-11-05T17:20:41Z")

</div>

Can be done in a change node using a jsonata expression. If you share the 2 input json messages in a format that can be copy pasted then we can give it a shot.

---

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [5 November 2020 19:05 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/3 "2020-11-05T19:05:06Z")

</div>

That would be great. I have no experience with jsonata (yet :-))

**[CODE QUERY 1: Greenchoice (Power supplier):**

```auto
[{"time":"1970-01-01T00:00:00.000Z","sum":472,"month_nb":"01","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":421,"month_nb":"02","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":215,"month_nb":"03","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":-50,"month_nb":"04","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":-62,"month_nb":"05","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":-17,"month_nb":"06","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":38,"month_nb":"07","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":152,"month_nb":"08","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":143,"month_nb":"09","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":322,"month_nb":"10","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":21,"month_nb":"11","year":"2020"}]

```

**[CODE QUERY 2: New Motion (Car Charger):**

```auto
[{"time":"1970-01-01T00:00:00.000Z","sum":271.28,"month_nb":"01","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":368.09,"month_nb":"02","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":200.17000000000002,"month_nb":"03","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":115.49,"month_nb":"04","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":196.45999999999998,"month_nb":"05","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":155.5,"month_nb":"06","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":156.97,"month_nb":"07","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":233.19,"month_nb":"08","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":151.24,"month_nb":"09","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":148.55,"month_nb":"10","year":"2020"}]

```

**[CODE QUERY 3: JOIN Greenchoice (power supplier)+ New Motion (Car Charger):**

```auto
{"newmotion":[{"time":"1970-01-01T00:00:00.000Z","sum":271.28,"month_nb":"01","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":368.09,"month_nb":"02","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":200.17000000000002,"month_nb":"03","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":115.49,"month_nb":"04","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":196.45999999999998,"month_nb":"05","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":155.5,"month_nb":"06","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":156.97,"month_nb":"07","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":233.19,"month_nb":"08","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":151.24,"month_nb":"09","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":148.55,"month_nb":"10","year":"2020"}],"greenchoice":[{"time":"1970-01-01T00:00:00.000Z","sum":472,"month_nb":"01","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":421,"month_nb":"02","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":215,"month_nb":"03","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":-50,"month_nb":"04","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":-62,"month_nb":"05","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":-17,"month_nb":"06","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":38,"month_nb":"07","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":152,"month_nb":"08","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":143,"month_nb":"09","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":322,"month_nb":"10","year":"2020"},{"time":"1970-01-01T00:00:00.000Z","sum":21,"month_nb":"11","year":"2020"}]}

```

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [5 November 2020 22:15 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/4 "2020-11-05T22:15:04Z")

</div>

Here an example solution

- [https://try.jsonata.org/stEfeOUFh](https://try.jsonata.org/stEfeOUFh)

Here below I have copy pasted the jsonata expression

```auto
/* 
    joined both arrays based on same month_nb and year.
    https://stackoverflow.com/questions/60788572/how-to-join-2-arrays-in-a-performant-way-using-jsonata
*/
Greenchoice@$A.New_Motion@$B[($A.month_nb=$B.month_nb) and ($A.year = $B.year)].{
    "time" : $A.time,
    "month_nb" : $A.month_nb,
    "year": $A.year,
    "Greenchoice_sum" : $A.sum,
    "New_Motion_sum" : $B.sum,
    "KwNet" : $A.sum - $B.sum
/* sort operator => https://docs.jsonata.org/path-operators#---order-by */
}^(year,month_nb)

```

It assumes that the input is a json object with following structure:

```auto
{
  "Greenchoice" : [array of Greenchoice values],
  "New_Motion" : [array of New_Motion values]
}

```

---

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [6 November 2020 19:39 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/5 "2020-11-06T19:39:15Z")

</div>

Thanks a lot! Amazing and very powerfull this JSONata. It looks like you can make SQL-like joins and ordering. I tried it yesterday for the first time and made this: "$zip([_.month\_nb],[_.year],[greenchoice.sum],[newmotion.sum])" what kind of worked but your solution is way better.

Just wondering and curious; is it possible to reuse the output of a JSON again in an embedded formula like (formula 1 (Formula 2))?

I will try to move the code to node red in a change node. It look promising so far.

---

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [6 November 2020 21:40 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/6 "2020-11-06T21:40:58Z")

</div>

I tried the code in Node Red but any JSONata code doesn't work. I tried to convert a Javascript Object to a JSON with the JSON Node and then a Change Node to obtain the values. The JSON seems OK but maybe somehow it is broken because when I just copy/paste from the debug and delete some rows it works OK. Any idea why the change node isn't working?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/4/644f61c45f630049db795172b6c55efb2ad2b054.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7d0bb7af0f9cb4a63e79cdf49ea36513b7520f75.png)

When I copy/paste the string out of the debug window and remove the last item and make the JSON complete again the Test outputs OK:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/e/3e8a2472487b2812a22c2d46c39269b9a5493456.png)

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [6 November 2020 22:43 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/7 "2020-11-06T22:43:36Z")

</div>

> [@ChillXXL](#):
>
> I tried to convert a Javascript Object to a JSON with the JSON Node

I think that is the problem. The JSON Node is converting the JSON object to a string while the change node is expecting as input a JSON object.

So I think it is fixed by removing the JSON node.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [6 November 2020 22:45 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/8 "2020-11-06T22:45:44Z")

</div>

> [@ChillXXL](#):
>
> Just wondering and curious; is it possible to reuse the output of a JSON again in an embedded formula like (formula 1 (Formula 2))?

Jsonata is very powerful. It is not exactly clear what you want. But if you clearly describe the input and output you want, then we might give it a shot.

---

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [6 November 2020 22:51 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/9 "2020-11-06T22:51:27Z")

</div>

Thanks. Removing the JSON node fixed my problem. I was joining with the JSON node because I didn't got any output and tried something out if it would solve my problem.

Meanwhile I figured out that my real issue was that my JSONata code was wrong.  
I needed a prefix of "payload". Now it works:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/4/84a168806a37fc2227b4ce661b6b27afdf2ab015.png)

---

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [6 November 2020 23:12 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/10 "2020-11-06T23:12:58Z")

</div>

_addition_; Is is possible with JSONata to create some sort of outer join where 1 array is leading? In my current query the greenchoice array has 11 (incl. november) items and the newmotion array has 10 items (till oktober).

Now the JSONata output is both 10 items ('till oktober). I haven't used my car charger of newmotion yet (november) so I would like that output of the newmotion row set to 0 in month\_nb = 11 and have a total output of 11 items.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [7 November 2020 00:02 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/11 "2020-11-07T00:02:03Z")

</div>

> [@ChillXXL](#):
>
> _addition_ ; Is is possible with JSONata to create some sort of outer join where 1 array is leading? In my current query the greenchoice array has 11 (incl. november) items and the newmotion array has 10 items (till oktober).

Yes that is possible:

- [https://try.jsonata.org/ykKlhG-jy](https://try.jsonata.org/ykKlhG-jy)

copy paste of the jsonata expression:

```auto
Greenchoice@$G.(
    $N := New_Motion[($G.month_nb=month_nb) and ($G.year = year)];
    $N_sum := $N.sum ~>$exists()?$N.sum:0;      
    {
        "month_nb" : $G.month_nb,
        "year": $G.year,
        "time": $G.time,
        "Greenchoice_sum" : $G.sum,
        "New_Motion_sum" : $N_sum,
        "KwNet" : $G.sum - $N_sum
     }
)^(year,month_nb)

```

---

<div class="post-metadata">

**Author:** ![ChillXXL](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chillxxl/32/30408_2.png) [@ChillXXL](https://discourse.nodered.org/u/ChillXXL)\
**Post date:** [9 November 2020 08:51 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/12 "2020-11-09T08:51:18Z")

</div>

Thanks again. Really great this JSONata.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [23 November 2020 08:51 UTC](https://discourse.nodered.org/t/how-to-merge-data-of-two-arrays-by-key-month-mapping-into-1-array-and-add-calculations/35382/13 "2020-11-23T08:51:34Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.