1

I have a Postgis (2.2.3 in PostgreSQL 9.5) database with a view that has aggregate array fields.

I can filter on these fields directly on the DB with 

Select * from myschema."ViewWithArrays" v
where '7415CM' = ANY(v."Postcodes")

But I cannot seem to find the syntax for filtering these array fields with CQL to my Geoserver (2.10.1) which has the view as a layer. http://docs.geoserver.org/stable/en/user/filter/ecql_reference.html#filter-ecql-reference

The IN keyword seems logical, but the following does not work:

CQL_FILTER=7415CM IN Postcodes

This gives the following error:

Could not parse CQL filter list. Encountered "7415 CM" at line 1, column 25. Was expecting one of: "not" ... "include" ... "exclude" ... "(" ... "[" ... "id" ... "in" ... ... ... ... "-" ... ... "*" ... "/" ... "+" ... "-" ... "not" ... "like" ... "ilike" ... "exists" ... "does-not-exist" ... "is" ... "between" ... "=" ... ">" ... "<" ... ">=" ... "<=" ... "<>" ... Parsing : VerzamelingId = 241 AND 7415CM IN Postcodes.

if you need the stacktrace of the geoserver, let me know, I will add it than

edit:

putting the statement 7415CM in single quotes does not help:

Could not parse CQL filter list. Encountered "\'7415CM\' IN" at line 1, column 25. Was expecting one of: "not" ... "include" ... "exclude" ... "(" ... "[" ... "id" ... "in" ... ... ... ... "-" ... ... ... ... "*" ... "/" ... "+" ... "-" ... "not" ... "like" ... "ilike" ... "exists" ... "does-not-exist" ... "is" ... "between" ... "=" ... ">" ... "<" ... ">=" ... "<=" ... "<>" ... Parsing : VerzamelingId = 241 AND '7415CM' IN Postcodes.

|improve this question
  • most probably you have a syntax error. it parses 7415CM (your where-filter) as a command. – nickves Jun 4 '18 at 9:47
  • than, what should the syntax be? and is it even possible to do this with the IN keyword? – Daniël Tulp Jun 4 '18 at 9:49
2

You must put the argument "7415 CM" within quotes, put the field name first and wrap the possible values in parentheses

Postcodes in ('7415CM')

Let's note that the error message added a space between the number and CM, meaning it is interpreting CM as a command, not as part of the parameter value.

|improve this answer
  • sorry to say that I had already tried that, error is in updated question – Daniël Tulp Jun 4 '18 at 14:24
  • @DaniëlTulp see updated answer – JGH Jun 4 '18 at 14:42
  • @DaniëlTulp For info: meta.stackexchange.com/questions/64068/… – JGH Jun 4 '18 at 14:43
  • thanks for the link and the solution seems to work, although now I have the problem that apparently, Geoserver does not like array fields..... (they are not listed when I want to create a layer from the view, I missed that when I did it the first time around), thanks for your answer – Daniël Tulp Jun 5 '18 at 6:33
  • GeoSErver indeed currently has no idea of what an array field is, and what to do with it. Some simple code needs to be written to add such support. – Andrea Aime Jun 5 '18 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.