I would like to offload just device inventory objects. So I tried to set the right filter predicate within the offloading job but do not succeed. Seems like DataHub puts either true or false in the exported column “c8y_IsDevice” dependent on the existence of c8y_IsDevice = {} in the MO.
So I tried following statements (and much more):
src.“c8y_IsDevice” IS true does not work.
src.“c8y_IsDevice” = true does not work.
src.“c8y_IsDevice” IS NOT NULL works, but of course does not do the right thing, because Data Hub always puts a value in the column.
I also tried casting to boolean or string within the filter expression.
Did anyone do this before? Filtering for not Boolean Columns seems to work as expected.
We just got the answer from R&D - It´s not that obvious. The solution could look like this: On mongo level exists a “_fragments” array, which contains all fragments of an inventory object. By seeking within the array for c8y_IsDevice you can filter for it:
convert_from(convert_to(“_fragments”, ‘JSON’), ‘UTF8’) LIKE ‘%“c8y_IsDevice”%’
there is no better solution currently. Would you mind to create an idea in Aha!? I’d actually like to see an option on CDH’s UI (“Devices only” checkbox or so). The solution posted here now leaks the internal MongoDB data model – that’s not good style and it might break anytime (it won’t most likely, but it could…)
We first tried it via e.g. ‘c8y_IsDevice is not NULL’ but that does somehow not work although it gives back the correct results within the Dremio UI.
We will create an idea about that. Additionally on top we would highly appreciate a more detailed “Best Practice” section within the documentation where queries for all typical use cases are listed (e.g. measurements contains fragments, Alarm before timestamp, event of certain type, device inventory, device with certain firmware etc. pp.)