# Grouping in Jsonata

**URL:** <https://discourse.nodered.org/t/grouping-in-jsonata/32304>\
**Category:** General\
**Created:** [1 September 2020 13:55 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304 "2020-09-01T13:55:14Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![BartButenaers](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bartbutenaers/32/10476_2.png) [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)\
**Post date:** [1 September 2020 13:55 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/1 "2020-09-01T13:55:14Z")

</div>

Hi folks,

In my daily job I need to do some calculations on a json, so I thought that Jsonata might perhaps be the solution for my problem.

I have a (very long) json, but here is a small part of it:

```auto
{
  "indices" : {
    "cas-acc-2020.08.09" : {
      "uuid" : "sN1jvqrUTfmue5s-EXldoQ",
      "primaries" : {
        "store" : {
          "size_in_bytes" : 1584455
        }
      },
      "total" : {
        "store" : {
          "size_in_bytes" : 3097060
        }
      }
    },
    "mdm-acc-2020.03.19" : {
      "uuid" : "JUG3LKd9RAKSHpNq_46WTA",
      "primaries" : {
        "store" : {
          "size_in_bytes" : 539462
        }
      },
      "total" : {
        "store" : {
          "size_in_bytes" : 1134716
        }
      }
    },
    "cas-acc-2020.08.08" : {
      "uuid" : "ZQyH-_6HSSmZsfrZ81RZCw",
      "primaries" : {
        "store" : {
          "size_in_bytes" : 1250445
        }
      },
      "total" : {
        "store" : {
          "size_in_bytes" : 2319444
        }
      }
    },
    "mdm-acc-2020.03.17" : {
      "uuid" : "vC0WuIhXT9WKXLUfGPuSFg",
      "primaries" : {
        "store" : {
          "size_in_bytes" : 625743
        }
      },
      "total" : {
        "store" : {
          "size_in_bytes" : 1278747
        }
      }
    }

```

If I need to calculate the total size in bytes, I can do it like this:

```auto
$sum(indices.*.total.store.size_in_bytes)

```

However I would like to have the sum (of size in bytes) per group of items, based on wildcards:  
For example I want to have the following two numbers in my output

- total size of "cas-acc-\*" = 5416504 (sum of 3097060 and 2319444)
- total size of "mdm-acc-\*" = 2413463 (sum of 1134716 and 1278747)

That way I can see the sum of total size of each group...

I only found [this](https://try.jsonata.org/ByxzyQ0x4), but that is still far from what I want to achieve.

Does anybody know how to do this?

Thanks!  
Bart

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [1 September 2020 17:29 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/2 "2020-09-01T17:29:18Z")

</div>

You could use the following expression (assuming it arrives as msg.payload)

```auto
{
"cas":$sum(payload.indices.$sift(function($v, $k) {$k ~> /^cas/}).*.total.store.size_in_bytes),
"mdm":$sum(payload.indices.$sift(function($v, $k) {$k ~> /^mdm/}).*.total.store.size_in_bytes)
}

```

Output:

```auto
{"payload":{"cas":5416504,"mdm":2413463}}

```

---

<div class="post-metadata">

**Author:** ![BartButenaers](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bartbutenaers/32/10476_2.png) [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)\
**Post date:** [1 September 2020 18:49 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/3 "2020-09-01T18:49:08Z")

</div>

Hey @bakman2,  
Thanks. How on earth did you figure out those expressions...  
I'm going to try these tomorrow morning at work.  
You have become my personal lifesaver this week 😉  
Bart

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [1 September 2020 20:40 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/4 "2020-09-01T20:40:58Z")

</div>

jsonata is nuts yet extremely powerful 😉

The documentation in general is bit rough, but [sift is nicely described](https://docs.jsonata.org/higher-order-functions#sift).

---

<div class="post-metadata">

**Author:** ![michaelblight](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/michaelblight/32/445_2.png) [@michaelblight](https://discourse.nodered.org/u/michaelblight)\
**Post date:** [2 September 2020 03:50 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/5 "2020-09-02T03:50:16Z")

</div>

I've seen plenty of nuts Jsonata - it does my head in. I only use it for straightforward transformations. I find anything fancy is way easier to understand later if it was written in a function node (maybe even with comments, god forbid).

---

<div class="post-metadata">

**Author:** ![BartButenaers](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bartbutenaers/32/10476_2.png) [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)\
**Post date:** [2 September 2020 06:22 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/6 "2020-09-02T06:22:12Z")

</div>

> [@michaelblight](#):
>
> easier to understand later if it was written in a function

Yes to be honest I also use function nodes to do this kind of stuff (for my home automation), but now it is for my daily job and using jsonata there fitted more easily in the process flow ...

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 September 2020 06:27 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/7 "2020-09-02T06:27:35Z")

</div>

> [@BartButenaers](#):
>
> I have a (very long) json, but here is a small part of it:

Bart, how long is long? I've found JSONata to be very very slow on large operations. Like 40s vs 1s in a function node on bigish data sets.

---

<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:** [2 September 2020 07:33 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/8 "2020-09-02T07:33:26Z")

</div>

> [@michaelblight](#):
>
> maybe even with comments, god forbid

1. Comments like `/* ... */` are also supported in jsonata.  
I think it often might help to add comments describing the output the jsonata query must generate.
2. Even more interesting than comments is the Test tab in the change node which allows to specify an Example message input and check immediately the output. As the example message in the Test tab is not stored in the flow (and lost after a restart), I am often copying it as a comment in the jsonata expression:

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

---

<div class="post-metadata">

**Author:** ![BartButenaers](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bartbutenaers/32/10476_2.png) [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)\
**Post date:** [2 September 2020 07:38 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/9 "2020-09-02T07:38:44Z")

</div>

> [@Steve-Mcl](#):
>
> Bart, how long is long?

Hey Steve, not long enough to become slow ... The query from @bakman2 is finished within one second and works fine to solve my problem at work.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [2 September 2020 08:18 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/10 "2020-09-02T08:18:47Z")

</div>

What I find with jsonata is there are good ways and bad ways for expressions, for simple things that are straight forward speed is most likely not an issue, when the dataset grows and the expression as well, it becomes more relevant to exactly know how jsonata performs its operation, which is a mystery to me, in those cases I seek help on the slack channel. I have seen really weird expressions, but applying an "incorrect" expression can add a lot of time.

I am sure there is a object/array limit where jsonata really starts to choke.

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [9 September 2020 14:55 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/11 "2020-09-09T14:55:36Z")

</div>

Hey Bart,

Here are a couple more ways to use JSONata to get the output you need...

When I see a payload that is one big object with many objects stored under different property names (e.g. `cas-acc-2020.08.09`), I usually start with the `$spread()` operator, to split up the outer object into an array of objects. Then I know there is only 1 key per object, and can modify that to allow grouping (i.e. by prefix string `cas-acc`):

```auto
payload.indices.$spread() {
    $keys($).$replace(/-20.*/, ""): $sum($.*.total.store.size_in_bytes)
}

```

Another technique is to start with a list of all the keys, and look up each object starting at the top of the payload:

```auto
payload.indices.$keys() {
    $.$replace(/-20.*/, ""): $lookup($$.payload.indices, $).total.store.size_in_bytes ~> $sum()
}

```

Although this syntax is probably easier to "read", it will be much less efficient, due to all of the lookups.

The main advantage to this approach is that you do not need to enumerate all of the desired groups ahead of time -- in this case, the replace function will be run against any keys that are present in the data.

---

<div class="post-metadata">

**Author:** ![BartButenaers](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bartbutenaers/32/10476_2.png) [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)\
**Post date:** [9 September 2020 16:43 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/12 "2020-09-09T16:43:04Z")

</div>

> [@shrickus](#):
>
> The main advantage to this approach is that you do not need to enumerate all of the desired groups ahead of time

Hey Steve,  
That is indeed an advantage, because I needed to do another query in advance to get all the available groups. Thanks!

---

<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 September 2020 16:43 UTC](https://discourse.nodered.org/t/grouping-in-jsonata/32304/13 "2020-09-23T16:43:35Z")

</div>

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