JSONata problem/challenge?

General
1

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

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:

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

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

{"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 ?

2

You can use context variable binding

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

Ah yes, that is very useful!

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

{"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.

4

Switching from Javascript to JSONata, & concatenates try using and

1 Like
5

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:

{"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
            }
        }}
    ]}
}}
1 Like
6

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.

{
   "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"]|
                        }|      
                    )
                }
            ]
        }
    }
}
1 Like
7

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.

8

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

{
   "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.

9

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