IN equivalent operator in painless inline scripts


(Shobhana Sriram) #1

I want to achieve the equivalent of the following SQL query using inline scripts of ES (painless language) :
where userId in (1, 2, 3) ----> Here userId is a long field

I tried with the following :
POST ride/ride/_search
{
"query": {
"bool": {
"filter": [
{
"script": {
"script": {
"source": "doc['userId'].value in [1, 2, 3]"
}
}
}
]
}
}
}

but saw an error :
"caused_by": {
"type": "script_exception",
"reason": "compile error",
"script_stack": [
"doc['userId'].value in [1, 2, 3]",
" ^---- HERE"
],
"script": "doc['userId'].value in [1, 2, 3]",
"lang": "painless",
"caused_by": {
"type": "illegal_argument_exception",
"reason": "invalid sequence of tokens near ['in'].",
"caused_by": {
"type": "no_viable_alt_exception",
"reason": null
}
}
}

Is there a way to achieve this using inline scripts? I know that this can be achieved using terms, but I am looking for a way to add this as part of another inline script query.

Thanks in advance.

Regards,
Shobhana


(David Pilato) #2

Why using a script for this?


(Shobhana Sriram) #3

I want to include this as a sub-query in another script which has several other conditions which I could not model without using the scripts. Some distinct part of the existing inline query is shown below :

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "startTime": {
              "lt": "Sep 24, 2018 1:05:04 PM"
            }
          }
        },
        {
          "range": {
            "expectedEndTime": {
              "gt": "Sep 24, 2018 12:12:04 PM"
            }
          }
        },
        {
          "script": {
            "script": {
              "source": "(doc['status'].value == 'Scheduled' || doc['status'].value == 'Started' || doc['status'].value == 'Delayed') && doc['distance'].value >= params.minDistance && doc['startPoint'].arcDistance(params.fromLat, params.fromLon) <= (doc['distance'].value >= params.rideDistance ? doc['distance'].value : params.rideDistance) * 1000",
              "params": {
                "toLat": 12.983942,
                "minDistance": 14.8253,
                "fromLon": 77.621174,
                "toLon": 77.477552,
                "fromLat": 13.044056,
                "maxDistance": 36.9432,
                "rideDistance": 20.3
              }
            }
          }
        }
      ]
    }
  }
} 

I want to include the query mentioned in my original question to the inline script mentioned above.


(Christian Dahlqvist) #4

Scripts run per document, and can not be used to run sub-queries.


(Shobhana Sriram) #5

Pardon my use of the word "sub-query" .. I just intended to say that I want to include the IN criteria as another condition in the existing script :

"source": "(doc['status'].value == 'Scheduled' || doc['status'].value == 'Started' || doc['status'].value == 'Delayed') && ( doc['userId].value IN [params.userIdList] || (doc['distance'].value >= params.minDistance && doc['startPoint'].arcDistance(params.fromLat, params.fromLon) <= (doc['distance'].value >= params.rideDistance ? doc['distance'].value : params.rideDistance) * 1000))"


(Christian Dahlqvist) #6

Would it not be easier, and possibly more performant, to do so using a terms query?


(Shobhana Sriram) #7

To be honest, I tried a lot writing a terms query first, but I couldn't figure out how to design my query to match the above. Later I found inline scripts section and prepared a query to match the above constraints. I couldn't identify how to mix && and|| together and also include max(field1, constant1) in the same query!


(Shobhana Sriram) #8

Is it possible to use IN operator in scripts?


(system) #9

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