Filter specific items that are alone in an array of Collection String in Azure Search

Question

I've been trying to develop a filter that should be quite easy, but it's been a while, and I'm not getting anywhere.

I have a property called 'test' that is a collection of strings. test can have ['X','Y','Z', ...] I want to filter out items that have only 'Z', meaning ['Z']. It should still fetch items as ['X,'Z'].

I tried a few ways but this is the last that I came up with and it's not working (still pulls both scenarios), any tips or ideas on how to solve this?

("test/all(t: not search.in(t, 'Z'))");

Answer 1

We can try using "test/any(t: t ne 'Z')", which should get results if any of the element inside array is not equal to Z.

But due to limitations like given here you can not use ne or not search.in() with any.

So, as an alternate solution you can convert this test field to strings and do filter.

Create a new field concat_test of type Strings.

then add field mappings like below in indexer.

    {
      "sourceFieldName": "test",
      "targetFieldName": "concat_test",
      "mappingFunction": null
    }

then reset and re-run the indexer.

You will get the array of strings as strings like below sample.

{
  "@odata.context": "https://xxx.search.windows.net/indexes('azureblob-index')/$metadata#docs(*)",
  "@odata.count": 3,
  "value": [
    {
      "@search.score": 1,
      "concat_test": "[\"X\",\"Y\",\"Z\",\"A\",\"B\"]"
    },
    {
      "@search.score": 1,
      "concat_test": "[\"Z\"]"
    },
    {
      "@search.score": 1,
      "concat_test": "[\"X\",\"Y\",\"Z\"]"
    }
  ]
}

Now query the exact string to filter.

{
  "search": "*",
  "count": true,
  "select": "test,concat_test",
  "filter": "concat_test ne '[\"Z\"]'"
}

Output:

{
  "@odata.context": "https://xxxxx.search.windows.net/indexes('azureblob-index')/$metadata#docs(*)",
  "@odata.count": 2,
  "value": [
    {
      "@search.score": 1,
      "test": [
        "X",
        "Y",
        "Z",
        "A",
        "B"
      ],
      "concat_test": "[\"X\",\"Y\",\"Z\",\"A\",\"B\"]"
    },
    {
      "@search.score": 1,
      "test": [
        "X",
        "Y",
        "Z"
      ],
      "concat_test": "[\"X\",\"Y\",\"Z\"]"
    }
  ]
}