Skip to main content
A
azoorob313-Aquamarine
13-Aquamarine
February 5, 2020
Solved

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

  • February 5, 2020
  • 2 replies
  • 2279 views

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

Best answer by azoorob3

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

2 replies

H
hchanana16-Pearl
16-Pearl
February 13, 2020

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

    A
    azoorob313-AquamarineAuthorAnswer
    13-Aquamarine
    February 18, 2020

    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

      Terms & ConditionsAccessibility statement