Elasticsearch 5.4: Use normal and nested fields in same Painless script query?

Question

I have a mapping like this:

{
  printings: { 
    type: "nested",
    properties: {
      prop1: {type: "number"}
    }  
  },
  prop2: {type: "number"}
}

I then want to build a Painless query like this:

"script": {
          "lang": "painless",
          "inline": "doc['prop1'] > (3 * doc['printings.prop2'])"
        }

However testing this in Sense doesn't work. If I replace the nested prop2 with a simple number then it does work. Is there a way to access both root and nested props in a single scripted query?

Answer 1

You can try below query.

{
"query": {
  "script": {
     "script": {
        "lang": "painless",
        "inline": "params['_source']['prop1'] > (2 * params['_source']['printings']['prop2'])"
     }
   }
 }
}

But please keep this thing mind that _source is very slow. read more about here

Answer 2

Unfortunately, you cannot access nested context from root and you cannot access root context from nested because nested documents are separate documents, even though they are stored close to the parent. But you can solve it with a different mapping using copy_to field feature. Here is a mapping:

{
    "mappings": {
        "sample": {
            "properties": {
                "printings": {
                    "type": "nested",
                    "properties": {
                        "prop2": {
                            "type": "integer",
                            "copy_to": "child_prop2"
                        }
                    }
                },
                "prop1": {
                    "type": "integer"
                },
                "child_prop2": {
                    "type": "integer"
                }
            }
        }
    }
}

In this case the values from nested documents will be copied to the parent. You don't have to explicitly fill this new field, here is ax example of bulk indexing:

POST http://localhost:9200/_bulk HTTP/1.1

{"index":{"_index":"my_index","_type":"sample","_id":null}}
{"printings":[{"prop2":1},{"prop2":4}],"prop1":2}
{"index":{"_index":"my_index","_type":"sample","_id":null}}
{"printings":[{"prop2":0},{"prop2":1}],"prop1":2}
{"index":{"_index":"my_index","_type":"sample","_id":null}}
{"printings":[{"prop2":1},{"prop2":0}],"prop1":2}

After that you can use this query

{
    "query": {
        "script": {
            "script": {
                "inline": "doc['prop1'].value > (3 * doc['child_prop2'].value)",
                "lang": "painless"
            }
        }
    }
}

The first document won't match. The second one will match by the first subdocument. The third one will match by the second subdocument.