Operator Advisor - Unable to query for null values using JSON filters

[azoorob3]

Hey Everyone,

I am currently using Operator Advisor 8.5.1.

I am unable to query for rows containing null values when using the Operator Advisor queries and JSON filters. When I query the tables directly using sql from the database thing, I am able to pull rows containing null values. However, when using OA queries/JSON filters, there is no way for me to pull these null values. I have attached a word document showing different filters I tried + the error messages.

Does anyone have an idea how I can pull rows containing  null/blank values from postgres database tables while using the Operator advisor queries? The MissingValue filter is not supported, which is what I'd use when querying a data table or stream. 

I want to avoid writing direct sql queries to do this if at all possible. 

Thanks,

Andrew

[hchanana]

Hi @azoorob3 ,

PTC Support services is working with you on the support case for the same issue reported by you.

We will post the solution once the case is resolved.

Thanks,

Himanshu

[azoorob3 · Accepted solution]

Hi All,

I was able to figure it out with the help of the support team. For Operator Advisor queries using the database Query service, you need to query for null values this way:

{

    "filters": {

        "fieldName": "endTime",

        "type": "MISSINGVALUE"

    }

}

MISSINGVALUE needs to be listed in all capitals, despite it being shown as MissingValue in the query parameter ThingWorx documentation. 

Andrew