cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Amethyst

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

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

View solution in original post

2 REPLIES 2
Highlighted

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

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

Highlighted

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

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

View solution in original post

Announcements