Comparision Filter issue for Observations: $filter=fieldName lt, does not return correct value

[rc-chandan]

This seems odd, I have created several observations for one datastream,

Observation that are present for datastream(1)

• water temperature at depth 5.5

  {
  "phenomenonTime": "2002-11-30T04:23:36Z",
  "resultTime": "2002-11-30T04:23:36Z",
  "result": 5.955,
  "resultQuality": 1,
  "parameters": {
  "positionIndicatorOfArgo": 3,
  "cycleNumber": 71,
  "dataMode": "D",
  "depth": 5.5,
  "depthFlag": 1,
  "depthAdj": 2.2,
  "depthAdjFlag": 1
  },
  "Datastream":{"@iot.id": 1}
  }

• water temperature at depth 9.4

  {
  "phenomenonTime": "2002-11-30T04:23:36Z",
  "resultTime": "2002-11-30T04:23:36Z",
  "result": 5.931,
  "resultQuality": 1,
  "parameters": {
  "positionIndicatorOfArgo": 3,
  "cycleNumber": 71,
  "dataMode": "D",
  "depth": 9.4,
  "depthFlag": 1,
  "depthAdj": 6.1,
  "depthAdjFlag": 1
  },
  "Datastream":{"@iot.id": 1}
  }

- Queries that are working:

1. /SensorThingsService/v1.0/Datastreams(1)/Observations?$filter=parameters/depth lt 6 Expectation : Should return the 1st obs Result : Returning the 1st obs

2. /SensorThingsService/v1.0/Datastreams(1)/Observations?$filter=parameters/depth gt 9 Expectation : Should return the 2nd obs Result : Returning the 2nd obs

- Queries that are not working properly:

1. /SensorThingsService/v1.0/Datastreams(1)/Observations?$filter=parameters/depth lt 10 Expectation : Should return both the observations Result : Returning none

2. /SensorThingsService/v1.0/Datastreams(1)/Observations?$filter=parameters/depth gt 10 Expectation : Should return none Result : Returning all the observations

NOTE: This is not specific to the numbers 6 or 10 only observations are not reliable for any comparison

I guess this is not only the problem with this implementation of SensorThings API, I found another implementation that has the same problem

Try this link

here Observations returned contains observations having field result > 2 also, while I have applied filter to restrict result < 2

[rc-chandan]

@hylkevds @mjacoby

[hylkevds]

It's similar to the scratchpad issue, but not quite the same. Scratchpad doesn't support filtering on sub-properties at all, while our implementation does correctly filter on the result property :)

The problem is string - number conversions and json columns in PostgreSQL: Observation/parameters is a JOSN object, stored as such in the database. When you filter on Observation/parameters/depth, we let the database do the filtering for us. The problem is that the PostgreSQL json functions always return text. Currently if one of the two operands of the compare is text, we treat both as text. Since the left operand (the json field) is text, we convert the number to text and when comparing strings, 5 and 9 are both greater than 10.

The SensorThings API doesn't specify at all how to deal with this situation at the moment, and filtering on sub-properties is a rather new feature, so it's not perfect yet.

A quick and dirty solution would be to 0-pad your numbers...

[hylkevds]

Filtering on json columns is not very fast in the default database layout, since it stores the json in a text field. You can convert the columns to jsonb for better performance. I've written a little howto on the wiki: https://github.com/FraunhoferIOSB/SensorThingsServer/wiki/Jsonb-columns

[hylkevds]

The latest version should now filter correctly on numeric fields in json properties. You can also sort as a number by forcing a numeric cast like this: $orderby=parameters/depth add 0 by adding 0, a numeric cast is forced. A limitation is that all values of depth must be null or numeric, otherwise a cast exception happens.

[hylkevds]

The latest version now has full support for accessing json properties, including accessing arrays and dealing with mixed data types. You get better performance if you convert the relevant columns from text to jsonb. If you use specific queries a lot, you can also add indices for those queries.

If you run into any bugs, feel free to open new issues for them :)