[SOLVED] Search array with AND azure cognitive search

Search array with AND azure cognitive search

I'm trying to figure out how to query in azure search using arrays with multiple matching values using AND logic.

The following query will return values matching categoryOne OR categoryTwo the boost score will be correctly sorted with the highest values having both at the top but i was wondering how i would restructure my query to use AND logic so it only returns documents where the field includes categoryOne and categoryTwo.

$filter=Categories/any(c: c eq 'categoryOne') AND Categories/any(c: c eq 'categoryTwo')

I've tried using multiple different queries but can't get it to work with the correct AND logic

s.searchIn('Categories', 'categoryOne') AND s.searchIn('Categories', 'categoryTwo')

I want to eventually have queries where i can combine this logic to find documents that have (categoryOne or categoryTwo) AND (categoryThree, or categoryFour or categoryFive) but struggling with getting AND to work.

This is the field property for my index

{
  "name": "Categories",
  "type": "Collection(Edm.String)",
  "searchable": true,
  "filterable": true,
  "retrievable": true,
  "stored": true,
  "sortable": false,
  "facetable": true,
  "key": false,
  "synonymMaps": []
}

Solution

The reason for the above query does not work is that Categories/any(c: c eq 'categoryOne') AND Categories/any(c: c eq 'categoryTwo') returns documents where at least one category equals categoryOne AND at least one category equals categoryTwo. this does not check that both values exist in the same array.

Use all instead of any. This query checks that both categoryOne and categoryTwo exist in the array.

$filter=Categories/all(c: c ne 'categoryOne') eq false AND Categories/all(c: c ne 'categoryTwo') eq false

If you want to extend this to (categoryOne OR categoryTwo) AND (categoryThree OR categoryFour OR categoryFive).

$filter=(Categories/all(c: c ne 'categoryOne') eq false OR Categories/all(c: c ne 'categoryTwo') eq false) 
AND (Categories/all(c: c ne 'categoryThree') eq false OR Categories/all(c: c ne 'categoryFour') eq false OR Categories/all(c: c ne 'categoryFive') eq false)