I am working with Elasticsearch and need to filter documents based on the value of a field in the first element of an array. Specifically, I have an array field called Tickers, and I want to check if the name field of the first element in this array matches any value from a provided list.

Here’s an example of the document structure:

{
  "Tickers": [
    {
      "name": "AAPL",
      "description": "description 0"
    },
    {
      "name": "GOOG",
      "description": "description 1"
    }
  ]
}

I need to filter documents where the name of the first element in the Tickers array is in a given list of ticker names (e.g., ["AAPL", "MSFT", "TSLA"]).

Here are my elasticsearch index mappings for Tickers Field:

{
   "Tickers":{
      "properties":{
      "name":{
            "type":"text",
            "fields":{
               "keyword":{
                  "type":"keyword",
                  "ignore_above":256
               }
            }
         },
         "description":{
            "type":"text",
            "fields":{
               "keyword":{
                  "type":"keyword",
                  "ignore_above":256
               }
            }
         }
         
      }
   }
}

What I've Tried

{
    "query": {
        "bool": {
            "filter": [
                {
                    "script": {
                        "script": {
                            "source": "doc['Tickers.name.keyword'].length > 0 && params.symbols.contains(doc['Tickers.name.keyword'].value)",
                            "params": {"symbols": ["TSLA"]}
                        }
                    }
                }
            ]
        }
    }
}

I am working with Elasticsearch and need to filter documents based on the value of a field in the first element of an array. Specifically, I have an array field called Tickers, and I want to check if the name field of the first element in this array matches any value from a provided list.

Here’s an example of the document structure:

{
  "Tickers": [
    {
      "name": "AAPL",
      "description": "description 0"
    },
    {
      "name": "GOOG",
      "description": "description 1"
    }
  ]
}

I need to filter documents where the name of the first element in the Tickers array is in a given list of ticker names (e.g., ["AAPL", "MSFT", "TSLA"]).

Here are my elasticsearch index mappings for Tickers Field:

{
   "Tickers":{
      "properties":{
      "name":{
            "type":"text",
            "fields":{
               "keyword":{
                  "type":"keyword",
                  "ignore_above":256
               }
            }
         },
         "description":{
            "type":"text",
            "fields":{
               "keyword":{
                  "type":"keyword",
                  "ignore_above":256
               }
            }
         }
         
      }
   }
}

What I've Tried

{
    "query": {
        "bool": {
            "filter": [
                {
                    "script": {
                        "script": {
                            "source": "doc['Tickers.name.keyword'].length > 0 && params.symbols.contains(doc['Tickers.name.keyword'].value)",
                            "params": {"symbols": ["TSLA"]}
                        }
                    }
                }
            ]
        }
    }
}

• elasticsearch
• search
• position

• 1 What issue are facing with this ? It seems like this query must work. – Sagar Patel Commented Nov 18, 2024 at 18:15
• For elasticsearch version 8.16, it works well – Paulo Commented Nov 18, 2024 at 20:34
• Yea, it works. Not sure why it wasn't working yesterday. Tried different solutions. – GM_1 Commented Nov 19, 2024 at 5:29

1 Answer 1

You could unscript the search query in the following way

Mapping with copy of the name field into a new field with the limit filter

PUT /first_ticker_searching
{
    "settings": {
        "analysis": {
            "analyzer": {
                "whitespace_limit_analyzer": {
                    "tokenizer": "whitespace",
                    "filter": [
                        "limit_filter"
                    ]
                }
            },
            "filter": {
                "limit_filter": {
                    "type": "limit"
                }
            }
        }
    },
    "mappings": {
        "properties": {
            "Tickers": {
                "properties": {
                    "name": {
                        "type": "text",
                        "copy_to": "first_ticker",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    },
                    "description": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    }
                }
            },
            "first_ticker": {
                "type": "text",
                "analyzer": "whitespace_limit_analyzer"
            }
        }
    }
}

Your and my sample documents

PUT /first_ticker_searching/_bulk
{"create":{"_id":1}}
{"Tickers":[{"name":"AAPL","description":"description 0"},{"name":"GOOG","description":"description 1"}]}
{"create":{"_id":2}}
{"Tickers":[{"name":"APLA","description":"description 0"},{"name":"AAPL","description":"description 1"}]}

Search span_first query

GET /first_ticker_searching/_search?filter_path=hits.hits
{
    "query": {
        "bool": {
            "filter": [
                {
                    "span_first": {
                        "match": {
                            "span_term": {
                                "first_ticker": "AAPL"
                            }
                        },
                        "end": 1
                    }
                }
            ]
        }
    }
}

Response. The first document is in the hits, the second one isn't

{
    "hits" : {
        "hits" : [
            {
                "_index" : "first_ticker_searching",
                "_type" : "_doc",
                "_id" : "1",
                "_score" : 0.0,
                "_source" : {
                    "Tickers" : [
                        {
                            "name" : "AAPL",
                            "description" : "description 0"
                        },
                        {
                            "name" : "GOOG",
                            "description" : "description 1"
                        }
                    ]
                }
            }
        ]
    }
}