Elasticsearch compare two fields

Question

For example MySQL query

SELECT fields_a, fields_b FROM table WHERE fields_a > fields_b;

I am trying to implement for elasticsearch. I've tried, as follows:

$where = [ "query" => [ "filter" => [ "script" => [ "script" => "doc[\"fields_a\"].value > doc[\"fields_b\"].value" ] ] ] ];

What am I missing?

Answer 1

this should work for me

{
    "query": {
        "bool": {
            "must": [{
                "script": {
                    "script": "doc['field_a'].value > doc['field_b'].value"
                }
            }]
        }
    }
}

To select only few fields instead of the whole source document use stored_fields "stored_fields": ["field_a","field_b"] and make sure to have those fields as store=true in mappings .

{
stored_fields": ["field_a","field_b"]
    "query": {
        "bool": {
            "must": [{
                "script": {
                    "script": "doc['field_a'].value > doc['field_b']"
                }
            }]
        }
    }
}