help need in righting rule

[robodevchampion]

Hi,

I want to write an rule on top of below message (which is an array of data)

{ "sensor1": [ { "ts": 1724231947811, "values": { "temperature": "200", "humidity": 40.0, "combine": "40:107" } }, { "ts": 1724231947811, "values": { "temperature": "10", "humidity": 41.0, "combine": "41:107" } } ] }

Now lets say I want to apply a rule where message with temperature < 150 should be filter out, can you please help me in writing SQL for this.

so result would be
{"ts":1724231947811,"values":{"combine":"41:107","humidity":41,"temperature":"10"}}

Thanks

[ngjaying]

I think you can use unnest to unnest the array row into multiple rows to memory sink and then use rule pipeline to filter each row.

[robodevchampion]

well thats ok. we can manage that on gateway level.

I tried to create a rule pipeline 1 rule for unnest and 2nd rule for checking out temperature.

Data In will be{"device_name":"sensor1","ts":"1724231947811","values":{"combine":"41:107","humidity":41,"temperature":"3"}}

Need rule for filtering out temperature>30, tried below

SELECT * FROM memory_stream 
where json_path_exists(values, "$.temperature > 30")

SELECT * FROM memory_stream 
where json_path_exists(values, "? @.temperature > 30")

Both gives all messages, please check once

Thanks

[ngjaying]

how about where values.temperature > 30

[robodevchampion]

well thats the first try I did,

SELECT * FROM memory_stream 
where values.temperature > 30

gives me below error
'{"error":"run Where error: invalid operation string(200) \\u003e int64(30)"}'

[ngjaying]

where cast(values.temperature, "bigint") > 30