Querying a list to check the size

Hello, I am just new to Kibana query. I am trying to query a field, which is a list of objects

"leadStages" : [
            {
              "leadStage" : 1,
              "inTime" : 1597832744832,
              "outTime" : 1597946057541
            },
            {
              "outTime" : 1598083851243,
              "inTime" : 1597946057541,
              "leadStage" : 2
            },
            {
              "inTime" : 1598083851243,
              "outTime" : 0,
              "leadStage" : 3
            }
          ],

In the above document field i.e. leadStages, I would like to fetch last element and check "inTime" value. The query I wrote

{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": """
              def stages = doc['leadStages'];
              def arrLen = stages.length-1;
              
              return (stages[arrLen].inTime == 0);
            """,
            "lang": "painless",
            "params": {
              "param1": 5
            }
          }
        }
      }
    }
  }
}

But the error I got is

"caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "No field found for [leadStages] in mapping"
          }

I then changed the query to


            "source": """
              def stages = doc['leadStages.inTime'];
              def arrLen = stages.length-1;
              
              return (stages[arrLen].value == 0);
            """,

This time the error was

"type" : "illegal_state_exception",
            "reason" : "A document doesn't have a value for a field! Use doc[<field>].size()==0 to check if a document is missing a field!"

I have already spent couple of hours on this. Your help would help a lot.

Did you try to see if your index needs to have this field mapped?

See Explicit mapping | Elasticsearch Guide [8.11] | Elastic

Yes, mapping there.

"leadStages" : {
          "type" : "nested",
          "properties" : {
            "inTime" : {
              "type" : "long"
            },
            "leadStage" : {
              "type" : "integer"
            },
            "outTime" : {
              "type" : "long"
            }
          }

Initially, I wondered: are there multiple indices matched in the search, which might include older indices that do not have the correct mapping?

After doing more research, I noticed a few things that apply here:

  1. The documentation lists the ways to interact with nested documents, and it doesn't mention they can be interacted with in a script: https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html#nested-accessing-documents
  2. The documentation states that nested objects are stored as separate documents, but the script query works on single documents at a time
  3. I found another post with a similar question, and the responder stated:

    You can not access the values of all nested objects in a script at query time. Your script query only works on one nested object at a time.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.