IoT sql query rule - comparing 2 arrays of objects returns null/undefined?

0

Greetings,
I'm trying to compare 2 arrays in a sql query. Everything seems to work fine while getting data from dynamodb and MQTT broker server.
But if I try to compare both arrays, doesn't return anything or its undefined.
My sql query:
SELECT ts, objects, (SELECT id FROM get_dynamodb('table', 'key_name', 'key_value', 'rolearn').ids) AS db, (SELECT id from objects) as obj_ids
FROM 'subscribed/topic'
WHERE objects != []

Result:
{
"ts": 1615807935588,
"objects": [
{
"id": 1,
"planet": "jupiter"
},
{
"id": 2,
"planet": "mars"
},
],
"db": [
{
"id": 2
},
{
"id": 3
}
],
"obj_ids": [
{
"id": 1
},
{
"id": 2
}
]
}

So far it's ok, now all I want to do is compare if "obj_ids" and "db" arrays are different (obj_ids != db), and by the aws documentation https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-operators.html#iot-sql-operators-not-eq I can compare both arrays.
So if I do:
SELECT ts, objects, (SELECT id FROM get_dynamodb('table', 'key_name', 'key_value', 'rolearn').ids) AS db, (SELECT id from objects) as obj_ids
FROM 'subscribed/topic'
WHERE objects != []
AND obj_ids != db

The code doesn't return anything. I've already tested comparing 2 objects arrays hardcoded into the query and it works just has I intended.
SELECT ([{"id": 1},{"id": 2}| != [{"id": 1}|) as result
FROM 'subscribed/topic'
(| => ])

Result:
{
"result": true
}

Any response will be appretiated.
Thanks!

asked 3 years ago336 views
1 Answer

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions