Elasticsearch script query involving root and nested values

Question

Suppose I have a simplified Organization document with nested publication values like so (ES 2.3):

{ 
  "organization" : { 
    "dateUpdated" : 1395211600000,

    "publications" : [ 
      { 
        "dateCreated" : 1393801200000
      },
      { 
        "dateCreated" : 1401055200000
      }
    ]
  }
}

I want to find all Organizations that have a publication dateCreated < the organization's dateUpdated:

{
  "query": {
    "nested": {
      "path": "publications",
      "query": {
        "bool": {
          "filter": [
            {
              "script": {
                "script": "doc['publications.dateCreated'].value < doc['dateUpdated'].value"
              }
            }
          ]
        }
      }
    }
  }
}

My problem is that when I perform a nested query, the nested query does not have access to the root document values, so doc['dateUpdated'].value is invalid and I get 0 hits.

Is there a way to pass in a value into the nested query? Or is my nested approach completely off here? I would like to avoid creating a separate document just for publications if necessary.

Thanks.

https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-fields.html Stored fields — fields explicitly marked as "store": true — can be accessed using the _fields['field_name'].value or _fields['field_name'].values syntax. — Liu, Oct 17 '16 at 22:35

score 7 · Accepted Answer · answered Oct 18 '16 at 02:51

You can not access the root values from nested query context. They are indexed as separate documents. From the documentation

The nested clause “steps down” into the nested comments field. It no longer has access to fields in the root document, nor fields in any other nested document.

You can get the desired results with the help of copy_to parameter. Another way to do this would be to use include_in_parent or include_in_root but they might be deprecated in future and it will also increase the index size as every field of nested type will be included in root document so in this case copy_to functionality is better.

This is a sample index

PUT nested_index
{
  "mappings": {
    "blogpost": {
      "properties": {
        "rootdate": {
          "type": "date"
        },
        "copy_of_nested_date": {
          "type": "date"
        },
        "comments": {
          "type": "nested",
          "properties": {
            "nested_date": {
              "type": "date",
              "copy_to": "copy_of_nested_date"
            }
          }
        }
      }
    }
  }
}

Here every value of nested_date will be copied to copy_of_nested_date so copy_of_nested_date will look something like [1401055200000,1393801200000,1221542100000] and then you could use simple query like this to get the results.

{
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": "doc['rootdate'].value < doc['copy_of_nested_date'].value"
          }
        }
      ]
    }
  }
}

You don't have to change your nested structure but you would have to reindex the documents after adding copy_to to publication dateCreated

Elasticsearch script query involving root and nested values

1 Answers1

Linked