# How do you group by a certain key value

**URL:** <https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585>\
**Category:** General\
**Created:** [22 December 2021 20:51 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585 "2021-12-22T20:51:10Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![hawks76](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@hawks76](https://discourse.nodered.org/u/hawks76)\
**Post date:** [22 December 2021 20:51 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/1 "2021-12-22T20:51:10Z")

</div>

I'm working on a flow that pulls down weather alerts from the NWS API and i'm trying to figure out how to group alerts. So, for instance, if there has been 3 severe weather alerts (initial alert and 2 alerts extending the expiration date), i want to group those, sort them, and pull the most recent by expiration date. I can do this in an exec node with powershell and pass the JSON back to Node-Red. But was wondering if there is a way to do it natively in Node-Red with either a function or a module.

Here is one that has multiple alerts of the same event type ("Winter Weather Advisory") but with different "expires" dates.

[https://api.weather.gov/alerts/active?zone=AKC068](https://api.weather.gov/alerts/active?zone=AKC068)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [22 December 2021 22:16 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/2 "2021-12-22T22:16:07Z")

</div>

What have you tried so far? Can you show us your flow?  
Also what version of NR are you running?

This sounds like a useful project!

---

<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:** [23 December 2021 02:21 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/3 "2021-12-23T02:21:55Z")

</div>

When I need to restructure some JSON data, I reach for a `change` node using a JSONata expression -- here is one that returns a single object with the latest timestamp for each event type:

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

The expression syntax is complex but powerful (it's like XSLT's xpath syntax, but for JSON data):

```auto
payload.features.properties^(>expires) {
    event: [expires][0]
}

```

In a nutshell, it says:

- Get all the **payload.features.properties** objects,
- sort them descending by `expires`
- build a single object with each **event** name as the field...
- and the first event `expires` as the value

---

<div class="post-metadata">

**Author:** ![hawks76](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@hawks76](https://discourse.nodered.org/u/hawks76)\
**Post date:** [23 December 2021 03:47 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/4 "2021-12-23T03:47:56Z")

</div>

Here is the flow i'm working with that has @shrickus solution he provided.

```auto
[{"id":"5076ebd24b9b3623","type":"tab","label":"Flow 1","disabled":false,"info":"","env":[]},{"id":"f9d47c5d2adf7166","type":"http request","z":"5076ebd24b9b3623","name":"Query Weather Service","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.weather.gov/alerts/active?zone=AKC090","tls":"","persist":false,"proxy":"","authType":"","senderr":false,"x":350,"y":200,"wires":[["bd2418ee33282d0e"]]},{"id":"6968d276d07c1eac","type":"inject","z":"5076ebd24b9b3623","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":200,"wires":[["f9d47c5d2adf7166"]]},{"id":"e549e8f7651e34d6","type":"comment","z":"5076ebd24b9b3623","name":"NWS Weather Alert Testing","info":"","x":190,"y":140,"wires":[]},{"id":"9be345f2b7a5cddc","type":"debug","z":"5076ebd24b9b3623","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":810,"y":200,"wires":[]},{"id":"bd2418ee33282d0e","type":"change","z":"5076ebd24b9b3623","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.features^(>expires) {\t event: [expires][0]\t}\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":200,"wires":[["9be345f2b7a5cddc"]]}]

```

Running v2.1.4 of Node-Red

---

<div class="post-metadata">

**Author:** ![hawks76](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@hawks76](https://discourse.nodered.org/u/hawks76)\
**Post date:** [23 December 2021 04:14 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/5 "2021-12-23T04:14:21Z")

</div>

How would I get the entire JSON branch for that? I'm not as well versed in this syntax as i would like to be.

---

<div class="post-metadata">

**Author:** ![hawks76](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@hawks76](https://discourse.nodered.org/u/hawks76)\
**Post date:** [23 December 2021 04:27 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/6 "2021-12-23T04:27:30Z")

</div>

also, just for some reference, here is my PowerShell code i call in an exec node:

```auto
$alerts = $null

$alerts = (Invoke-WebRequest -Uri "https://api.weather.gov/alerts/active?zone=AKC090" | ConvertFrom-Json).features.properties | Group-Object Event

if ($alerts -eq $null) 
{
    #do nothing, there are not alerts    
}

foreach ($alert in $alerts)
{
    if ($alert.Count -gt 1)
    {
        ($alert.Group | Sort-Object Expires)[-1] | ConvertTo-Json
    }
    else
    {
       $alert.Group | ConvertTo-Jsone
    }
}

```

my goal is to produce a string from the JSON like this that i can send to TTS and play out of my sonos speakers and send to Pushover.

The National Weather Service out of Fairbanks has issued a Moderate Winter Weather Advisory for Deltana and Tanana Flats effective until Thursday, December 23rd, 2021 09:00:00 AM

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [23 December 2021 08:05 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/7 "2021-12-23T08:05:29Z")

</div>

Two things:

1. in your flow the change node uses

```auto
payload.features^(>expires) {
    event: [expires][0]
}

```

while @shrickus example's is:

```auto
payload.features.properties^(>expires) {
    event: [expires][0]
}

```

(note the missing'property')  
2) you need to check if `msg.payload.features` is `empty` or the change node will get this error:

```auto
"Invalid JSONata expression: Key in object structure must evaluate to a string; got: undefined"

```

So add a `switch` node after the `http request` node to check that there is data to process. Here is the flow with the switch node added:

```auto
[{"id":"fef5cc626098b342","type":"http request","z":"d94067cbd6def60f","name":"Query Weather Service","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.weather.gov/alerts/active?zone=AKC090","tls":"","persist":false,"proxy":"","authType":"","senderr":false,"x":200,"y":200,"wires":[["0e8bc8b3af167e81"]]},{"id":"68b324e2ec702aeb","type":"inject","z":"d94067cbd6def60f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":140,"wires":[["fef5cc626098b342"]]},{"id":"c31c2e258eeca7ad","type":"comment","z":"d94067cbd6def60f","name":"NWS Weather Alert Testing","info":"","x":190,"y":80,"wires":[]},{"id":"b17e4b9c323eccae","type":"debug","z":"d94067cbd6def60f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":750,"y":200,"wires":[]},{"id":"ea48d5d589bcb58e","type":"change","z":"d94067cbd6def60f","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.features","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":180,"wires":[["b17e4b9c323eccae"]]},{"id":"0e8bc8b3af167e81","type":"switch","z":"d94067cbd6def60f","name":"","property":"payload.features","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":390,"y":200,"wires":[["ea48d5d589bcb58e"],["00d92f5cc9208133"]]},{"id":"00d92f5cc9208133","type":"change","z":"d94067cbd6def60f","name":"no alerts","rules":[{"t":"set","p":"payload","pt":"msg","to":"No current alerts","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":220,"wires":[["b17e4b9c323eccae"]]}]

```

---

<div class="post-metadata">

**Author:** ![hawks76](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@hawks76](https://discourse.nodered.org/u/hawks76)\
**Post date:** [23 December 2021 13:24 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/8 "2021-12-23T13:24:34Z")

</div>

Yeah, I was tinkering around with the code trying to get it to display the full announcements and forgot to add it back. Sorry about that.

---

<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:** [23 December 2021 15:46 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/9 "2021-12-23T15:46:09Z")

</div>

> [@hawks76](#):
>
> How would I get the entire JSON branch for that?

I was wondering if that was what you really wanted ;\*)

It looks like the powershell script sorts the list of objects, and just returns the latest one -- is that right? If so, the expression would change to be:

```auto
(payload.features^(>properties.expires) {
    properties.event: [$][0]
}).*

```

Conceptually, it finds all **features** , sorts them desc by **expires** , groups by **event** , and takes the first one from each group. Is this closer to what you need?

---

<div class="post-metadata">

**Author:** ![hawks76](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@hawks76](https://discourse.nodered.org/u/hawks76)\
**Post date:** [23 December 2021 17:43 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/10 "2021-12-23T17:43:54Z")

</div>

Yes sir! That's exactly what I'm looking for. Now that i see the code it makes total sense.

Thank you for taking the time to help out with this!! I do appreciate it!

---

<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:** [6 January 2022 17:44 UTC](https://discourse.nodered.org/t/how-do-you-group-by-a-certain-key-value/55585/11 "2022-01-06T17:44:11Z")

</div>

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