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

General
1

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

Query 2 [array (key/value)]: New Motion (my car charger) in kWh:
image

JOIN RESULTS (by msg.topic):
image
21 (total of) objects instead of the max 12 (months in a year)

2

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.

3

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

[CODE QUERY 1: Greenchoice (Power supplier):

[{"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):

[{"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):

{"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"}]}
4

Here an example solution

Here below I have copy pasted the jsonata expression

/* 
    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:

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

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.

6

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
image1653Ă—452 47 KB

image
image1106Ă—828 49.9 KB

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
image1335Ă—813 45.1 KB

7

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.

8

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.

9

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
image1152Ă—475 30.6 KB

2 Likes
10

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.

11

Yes that is possible:

copy paste of the jsonata expression:

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)
1 Like
12

Thanks again. Really great this JSONata.

1 Like
13

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