Filtering issue for Things: $filter=properties/fieldname eq value does not work about frost-server HOT 15 CLOSED

The error message "Unexpected 'I'" is a bit unexpected, I would expect a "Invalid query: Query is not valid." since filtering on fields in properties is not yet supported.

I'm guessing you're filtering all Things, not just Thing(9) :)

For now you can try
$filter=substringof(%27"template":"true"%27,properties)

It will do a string search on the json object stored for the properties field. Far from ideal, since it's not very reliable.
I'll mark this as a feature request.

from frost-server.

Yes - I want to filter all things, not just Thing(9) :)

Thank you for your response. I was not quite clear on how deep the conformance requirements for filtering goes.
To provide a bit more information on my use case I want to model types of devices as templates, which a provisioning client then can look up, select the appropriate template, and then use this template to instantiate a new model for the provisioned device. Thanks

from frost-server.

That sounds like an interesting use-case.

Currently a more reliable option would be to prefix the name of these templates with "template-" or something like that.

The conformance tests currently do not check if filtering on sub-properties works. While implementing this I'll create some tests for it in our branch of the conformance tests, and create a pull request for it. That should get a discussion started about it.

from frost-server.

Thank you for the advice.
In my current experiments I have prefixed the Thing description with "@template@".

Thank you for your plan to extend your tests

from frost-server.

Shall I keep this open or do you prefer to have it closed?

from frost-server.

I've added some first support for filtering and ordering on json properties. Please give the latest version a spin and let me know if it works as you expect.

PostgreSQL always treats json properties as text, so ordering on numeric properties will order them in string order (0,1,10,11,2,3).

We also need to look into converting the columns that hold json string to jsonb to increase performance.

Please keep this issue open, I'll close it when I'm happy with the implementation :)

from frost-server.

Great! My Dev team is off on vacation so it will probably be a week or so before I can have your new version up and run.

from frost-server.

Hi hylkevds
I find out a problem.
if you use the following URL:
http://localhost:8080/SensorThingsServer/v1.0/Things(9)?$filter=properties/name eq 'My models name'
the response I get from the latest version is Unexpected 'I'.

Hoping somebody can help here. Thanks!

from frost-server.

It seems I only pushed the commit to the intervalFunctions branch. I've now also pushed it to the master branch.

There is a second issue in that it currently doesn't like it if a property has a key that is also used as an entityproperty in SensorThings API. So properties/name doesn't currently work, since "name" is an entityProperty, likewise properties/Things also doesn't work, since Things is a navigationProperty.

from frost-server.

It should work now on the latest version of the master branch.

from frost-server.

Thank you for your response.
It work.

from frost-server.

Hi Hylke van der Schaaf,
I have now tried your latest version, and it works as I expect.
Good work.

from frost-server.

The latest version now has full support for accessing json properties, including accessing arrays and dealing with mixed data types.

from frost-server.

Hello,

unfortunately I would like the reopen this for a minor inquiry. Requesting "$filter=properties/fieldname eq value" where the fieldname contains underscores i.e. "external_project_id" yields in an error. Without underscores everything works. Since I would like to keep everything in lower case I think the possibility of underscores will help for a better clarity/usability of the keys.

from frost-server.

I've added the underscore to the list of allowed characters in names.

from frost-server.