enter image description here

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: query

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


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

| improve this answer | |
  • The problem is the follow: if i would more value inside function any , it get an error. – drcimux Aug 4 '17 at 9:39
  • The problem is the follow: if i would more value inside function any , it get an error. I would like something of this: entity_type eq 'store' and (ancestors/any(t: t eq 'region:1094' , 'store:361' ) or (entity_id eq 'region:1094')). I want entity that have ancestor that contains for example 'region:1094' or 'store:361' . – drcimux Aug 4 '17 at 9:45
  • @drcimux I have updated the answer to include a sample that will probably do what you're looking for. Again, I refer you to the fact that only simple lambdas are supported in the any() function, so for anything more complicated than the above you'll need to use the more expressive, but dramatically different, Lucene syntax – Richard Hauer Aug 4 '17 at 10:55
  • I use version 2016-09-01 and if I use search.in i got this error :{ "error": { "code": "", "message": "Invalid expression: An unknown function with name 'eq' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\nParameter name: $filter" } } . If i write only in doesnt work. – drcimux Aug 4 '17 at 12:59
  • Can you post your actual query, or is it exactly as I wrote it above? – Richard Hauer Aug 4 '17 at 21:41

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.