# JSONata problem/challenge?

**URL:** <https://discourse.nodered.org/t/jsonata-problem-challenge/68748>\
**Category:** General\
**Created:** [9 October 2022 07:12 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748 "2022-10-09T07:12:50Z")\
**Posts on this page:** 9\
**Page:** 1

<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:** [9 October 2022 07:12 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/1 "2022-10-09T07:12:50Z")

</div>

I am trying to combine elements from payload properties, but the more i look at it, the more confused I get 😬

The concept:  
There are rooms, rooms have devices, devices have properties.  
Changes on the properties as stored in the `hasProp` property

I have this object:

```auto
{
  "payload": {
    "rooms": [
      {
        "devices": [
          "device:netatmo_outdoor"
        ],
        "id": "room:balcony",
        "name": "balcony"
      }
    ],
    "device": [
      {
        "id": "device:netatmo_outdoor",
        "measures": [
          "prop:temperature",
          "prop:humidity",
          "prop:battery",
          "prop:pressure"
        ],
        "name": "netatmo_outdoor"
      }
    ],
    "prop": [
      {
        "id": "prop:battery",
        "name": "battery",
        "unit": "%"
      },
      {
        "id": "prop:co2",
        "name": "CO2",
        "unit": "CO2"
      },
      {
        "id": "prop:humidity",
        "name": "humidity",
        "unit": "%"
      },
      {
        "id": "prop:linkquality",
        "name": "link quality",
        "unit": "LQI"
      },
      {
        "id": "prop:motion",
        "name": "motion"
      },
      {
        "id": "prop:noise",
        "name": "noise",
        "unit": "dB"
      },
      {
        "id": "prop:on",
        "name": "on",
        "unit": "-"
      },
      {
        "id": "prop:pressure",
        "name": "pressure",
        "unit": "hP"
      },
      {
        "id": "prop:temperature",
        "name": "temperature",
        "unit": "C"
      }
    ],
    "hasProp": [
      {
        "in": "device:netatmo_outdoor",
        "out": "prop:temperature",
        "timestamp": "2022-10-06T08:38:45.576273770Z",
        "value": 21.85
      }
    ]
  }
}

```

and I would something like this as output:

```auto
[
    {
        "room": "livingroom",
        "devices": [
            {
                "device": "netato_outdoor",
                "properties": [
                    {
                        "property": "temperature",
                        "unit": "C",
                        "values": [
                            {
                                "value": 21.1,
                                "timestamp": "2022-10-06T06:09:08.415Z"
                            }
                        ]
                    }, 
                    { ... }
                ]
            },
            { ... }
        ]
    },
    { ... }
]

```

I had an expression like:

```auto
{"payload": *.rooms{
    $.name: {"devices":[
        **.devices.{
            "device": $,
            "measurements":{"value":$$.payload.hasProp[in=$].value, "timestamp": $$.payload.hasProp[in=$].timestamp}
        }
    ]}
}}

```

The positional `hasProp[in=$]` in measurements does not want to work, I guess because of the context that has changed, but how do I get the values out of it ?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 October 2022 08:21 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/2 "2022-10-09T08:21:22Z")

</div>

You can use [context variable binding](https://docs.jsonata.org/path-operators#-context-variable-binding)

```auto
{"payload": *.rooms{
    $.name: {"devices":[
        **.devices@$i.{
            "device": $i,
            "measurements":{"value":$$.payload.hasProp[in=$i].value, "timestamp": $$.payload.hasProp[in=$i].timestamp}
        }
    ]}
}}

```

---

<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:** [9 October 2022 08:54 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/3 "2022-10-09T08:54:40Z")

</div>

Ah yes, that is very useful!

I am nearly there, but I need to match 2 values, is that also possible in an expression ?

```auto
{"payload": *.rooms{
    $.name: {"devices":[
        $.devices@$i.{
            "device": $i,
            "properties": $$.payload.device[id=$i].measures@$x.{
                "id":$$.payload.prop[id=$x].id,
                "name":$$.payload.prop[id=$x].name,
                "unit":$$.payload.prop[id=$x].unit,
                "measurements":[
                    {
                        "value":$$.payload.hasProp[in=$i & out=$x].value,
                        "timestamp":$$.payload.hasProp[in=$i & out=$x].timestamp
                    }]
            }
        }
    ]}
}}

```

This part:  
`$$.payload.hasProp[in=$i & out=$x].value`  
No results, but no errors either.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 October 2022 09:00 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/4 "2022-10-09T09:00:11Z")

</div>

Switching from Javascript to JSONata, `&` concatenates try using `and`

---

<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:** [9 October 2022 09:13 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/5 "2022-10-09T09:13:47Z")

</div>

Truly awesome many thanks, been dealing with this data for the last 3 days. Data comes out of surealdb, I did not manage to produce a query with the same results, then tried javascript with forEach, lookups, ran into issues with object references. JSONata to the rescue once again.

Final expression:

```auto
{"payload": *.rooms{
    $.name: {"devices":[
        $.devices@$device.{
            "device": $device,
            "properties": $$.payload.device[id=$device].measures@$prop.{
                "id":$$.payload.prop[id=$prop].id,
                "name":$$.payload.prop[id=$prop].name,
                "unit":$$.payload.prop[id=$prop].unit,
                "measurements":$$.payload.hasProp[in=$device and out=$prop].{
                        "value":$.value,
                        "timestamp":$.timestamp
            }
        }}
    ]}
}}

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 October 2022 14:00 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/6 "2022-10-09T14:00:05Z")

</div>

Just for info.

I had a play with this as i had a little time this afternoon.  
This may be a more dynamic solution, you can alter property names if you wish to, using the transform syntax.

```auto
{
   "payload": $$.payload.rooms{
       $.name: {
           "devices":[
               $.devices@$device.{
                   "device": $device,
                   "properties": $$.payload.device[id=$device].measures@$prop.(
                        $$.payload.prop[id=$prop] ~> |$|{
                            "measurements":$$.payload.hasProp[in=$device and out=$prop] ~> |$|{},["in","out"]|
                        }|      
                    )
                }
            ]
        }
    }
}

```

---

<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:** [9 October 2022 16:09 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/7 "2022-10-09T16:09:40Z")

</div>

Nice, a bit cleaner, although I am not sure if I want all properties listed once everything gets its place. Then again, it is better to have everything available.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 October 2022 16:12 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/8 "2022-10-09T16:12:57Z")

</div>

You can delete unwanted properties just add a delete array with list of unwanted

```auto
{
   "payload": $$.payload.rooms{
       $.name: {
           "devices":[
               $.devices@$device.{
                   "device": $device,
                   "properties": $$.payload.device[id=$device].measures@$prop.(
                        $$.payload.prop[id=$prop] ~> |$|{
                            "measurements":$$.payload.hasProp[in=$device and out=$prop] ~> |$|{},["in","out"]|
                        },["id"]|      
                    )
                }
            ]
        }
    }
}

```

Should remove id from returned object.

---

<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 October 2022 16:13 UTC](https://discourse.nodered.org/t/jsonata-problem-challenge/68748/9 "2022-10-23T16:13:23Z")

</div>

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