ODATA search against a string collection field, searching for one or more values

Question

I've this document called entities. I would like to execute this query that contains "IN" to allow me to select a document based on whether one or more values are contained in a string list field:

Sample shown includes the value 'region:1094' but it is possible we need to search against more than one value.

Using the code above, I get this error:

{ "error": { "code": "", "message": "Invalid expression: ')' or ',' expected at position 46 in 'entity_type eq 'store' and (ancestors/any(t:t in 'region:1094') or entity_id eq 'region:1094')'.\r\nParameter name: $filter" } }

My version is 2016-09-01

Answer 1

Going to take a punt based on the ODATA syntax operators listed at https://docs.microsoft.com/en-us/rest/api/searchservice/odata-expression-syntax-for-azure-search

This appears to suggest that any() and all() functions don't support arbitrary lambdas but only those of simple equality or with the search.in() function, which is probably not what you want. If I'm right in guessing that you would like any document that contains the value 'region:1094' within the Collection(Edm.String) value for the ancestors field you will probably get the result you're looking for from something along the lines of the following:

entity_type eq 'store' and (ancestors/any(t: t eq 'region:1094') or (entity_id eq 'region:1094'))

In plain English I would describe this search as any document such that:

• the entity_type value is 'store', and
• either
  • the entity_id value is 'region:1094', or
  • the value 'region:1094' is contained in the list of values in the ancestors field

It's not clear why you have decided you must use the in operator (not even totally sure there is an in operator in ODATA actually). Perhaps you can clarify that in your question? Typically the any() or all() functions accept a lambda to be executed against all the elements of a list, and pass that value to the lambda (that's the t: bit in your query). in would seem redundant in this context, right? Unless you mean that region:1024 is only part of the value you're searching for. In which case you should try the functions startswith(myField,'val'), endswith(myField,'val') or substringof(myField,'val'). Note that while these are all valid ODATA operators the doco above explicitly states that only simple equality operators are supported by the all() and any() functions.

EDIT: To extend the above search to cover two fields (assuming these are 'equals' matches and not partial matches) such as 'region:1094' and 'store:361' you can use something like the following:

entity_type eq 'store' and (ancestors/any(t: search.in(t,'region:1094|store:361','|') ) or (entity_id eq 'region:1094'))

The search.in() function will return true if the value in the first parameter (the field) matches any of the values in the second parameter which should be comma-separated. If supplied, the third parameter (in the above case, |) swaps a comma list item separator for whatever is supplied. I prefer the pipe since it's less likely to appear as a value - I assume your values are user-generated in Sitecore.

Otherwise, swapping to the Lucene syntax will support the expressiveness of the query you are trying to form? Try doco here