Painless Elasticsearch Script for Filtering Nested Array Objects

Question

My use case is something similar to following. I have nested array of objects warehouses and trying to filter based on the last element of the array.

I am getting some results but not correct one. Like to know how exactly it is working though as well.

Let's say,

I want to search for a product based on stocks last element of warehouse array. This is the product document looks like:

{
  "productId": 5,
  "productName": "Shoes",
  "warehouses": [
    {
      "location": "Location A",
      "quantity": 100
    },
    {
      "location": "Location B",
      "quantity": 10
    },
    {
      "location": "Location C",
      "quantity": 50
    }
  ]
}

And it's mapping is :

PUT /products
{
  "mappings": {
    "properties": {
      "productId": {
        "type": "integer"
      },
      "productName": {
        "type": "text",
        "fields": {
            "raw": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "warehouses": {
        "properties": {
          "location": {
            "type": "text"
          },
          "quantity": {
            "type": "integer"  
          }
        }
      }
    }
  }
}

Let's say, I index following 7 documents:

POST products/_bulk
{"index":{"_id":1}}
{"productId":1,"productName":"Bags","warehouses":[{"location":"Location A","quantity":20},{"location":"Location B","quantity":30},{"location":"Location C","quantity":50}]}
{"index":{"_id":2}}
{"productId":2,"productName":"Shirts","warehouses":[{"location":"Location A","quantity":100},{"location":"Location B","quantity":150},{"location":"Location C","quantity":150}]}
{"index":{"_id":3}}
{"productId":3,"productName":"Shoes","warehouses":[{"location":"Location A","quantity":100},{"location":"Location B","quantity":10},{"location":"Location C","quantity":50}]}
{"index":{"_id":4}}
{"productId":4,"productName":"Shirt","warehouses":[{"location":"Location A","quantity":100},{"location":"Location B","quantity":10},{"location":"Location C","quantity":60}, {"location":"Location F","quantity":70}]}
{"index":{"_id":5}}
{"productId":5,"productName":"Socks","warehouses":[{"location":"Location A","quantity":800},{"location":"Location B","quantity":1500},{"location":"Location Z","quantity":1000}]}
{"index":{"_id":6}}
{"productId":6,"productName":"TV","warehouses":[{"location":"Location A","quantity":20},{"location":"Location B","quantity":150},{"location":"Location C","quantity":123}]}
{"index":{"_id":7}}
{"productId":7,"productName":"Table","warehouses":[{"location":"Location A","quantity":20},{"location":"Location B","quantity":200},{"location":"Location C","quantity":140}, {"location":"Location D","quantity":123}]}

Now I would like to search and filter products with "quantity": 123. So as per above indexed documents, I suppose to filter and get the products with id:6 and id:7, because it has the quantity: 123 as its last element.

Here is my Painless(full) script:

GET /products/_search
{
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": {
        "bool": {
          "must": {
            "script": {
              "script": {
                "lang": "painless",
                "source": """
                  def x = doc['warehouses.quantity'];

                  def flag = false;
                    if(x[x.length - 2 ] == params.limit) {
                      flag = true;
                    }
                  
                  return flag;
                """,
                "params": {
                  "limit": 123
                }
              }
            }
          }
        }
      }
    }
  }
}

So in above script I get result for id:6 which is TV product. And when I replace x[x.length - 2 ] with x[x.length - 3 ] I can get result for id:7.

I am not sure how to get result which contains both documents, [id:6 (TV) and id:7 (Table)].

I am using Elasticsearch version: 7.8.1.

Val · Answer 1 · 2021-06-09T05:55:14.150

This is because your warehouses array is not of nested type, hence the order of the elements within that array is not guaranteed (it is actually sorted in ascending order of the values). You can see this easily by running the following query and you'll witness that 123 is not necessarily in the last position:

GET /products/_search
{
  "docvalue_fields": ["warehouses.quantity"]
}

Response:

  {
    "_index" : "products",
    "_type" : "_doc",
    "_id" : "6",
    "_score" : 1.0,
    "_source" : {
       ...
    },
    "fields" : {
      "warehouses.quantity" : [
        20,
        123,
        150
      ]
    }
  },
  {
    "_index" : "products",
    "_type" : "_doc",
    "_id" : "7",
    "_score" : 1.0,
    "_source" : {
       ...
    },
    "fields" : {
      "warehouses.quantity" : [
        20,
        123,
        140,
        200
      ]
    }
  }

You need to change your mapping

PUT /products
{
  "mappings": {
    "properties": {
      "productId": {
        "type": "integer"
      },
      "productName": {
        "type": "text",
        "fields": {
            "raw": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "warehouses": {
        "type": "nested",           <--- add this
        "properties": {
          "location": {
            "type": "text"
          },
          "quantity": {
            "type": "integer"  
          }
        }
      }
    }
  }
}

And then your query can look like this and return both documents 6 and 7:

GET /products/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "warehouses",
            "query": {
              "script": {
                "script": {
                  "source": """
                      def x = doc['warehouses.quantity'];
                      return x[-1] == params.limit;
                """,
                  "params": {
                    "limit": 123
                  }
                }
              }
            }
          }
        }
      ]
    }
  }
}

Quick tip: x[-1] allows you to access the last element of the array whatever length it might have.

score 0 · Answer 2 · answered Jun 09 '21 at 12:16

Thanks for the answer @Val,

I tried to solve it with functionScore query:

GET products/_search
{
  "min_score": 0.1,
  "query": {
    "function_score": {
      "query": {
        "match_all": {}
      },
      "functions": [
        {
          "script_score": {
            "script": {
              "source": """
                def last = params['_source']['warehouses'].length - 1;
                
                def quantityOfLast = params._source['warehouses'].get(last);
                
                if (quantityOfLast.quantity == params.limit) {
                  return 1;
                } else {
                  return 0;
                }
                
""",
              "params": {
                "limit": 70
              }
            }
          }
        }
      ]
    }
  }
}

I'm curious what didn't work with my answer? the function_score query is not meant for that purpose. You should go the way I suggest — Val, Jun 15 '21 at 06:31

score 0 · Answer 3 · answered Jun 15 '21 at 10:26

If you change your mapping with nested type as @Val said you can avoid completely the painless script and use a simple nested query:

New Mapping:

PUT /products
{
  "mappings": {
    "properties": {
      "productId": {
        "type": "integer"
      },
      "productName": {
        "type": "text",
        "fields": {
            "raw": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "warehouses": {
        "type": "nested", 
        "properties": {
          "location": {
            "type": "text"
          },
          "quantity": {
            "type": "integer"  
          }
        }
      }
    }
  }
}

Query:

GET products/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "warehouses",
            "query": {
              "term": {
                "warehouses.quantity": {
                  "value": "123"
                }
              }
            }
          }
        }
      ]
    }
  }
}

Result:

{
  "took" : 14,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "products",
        "_type" : "_doc",
        "_id" : "6",
        "_score" : 0.0,
        "_source" : {
          "productId" : 6,
          "productName" : "TV",
          "warehouses" : [
            {
              "location" : "Location A",
              "quantity" : 20
            },
            {
              "location" : "Location B",
              "quantity" : 150
            },
            {
              "location" : "Location C",
              "quantity" : 123
            }
          ]
        }
      },
      {
        "_index" : "products",
        "_type" : "_doc",
        "_id" : "7",
        "_score" : 0.0,
        "_source" : {
          "productId" : 7,
          "productName" : "Table",
          "warehouses" : [
            {
              "location" : "Location A",
              "quantity" : 20
            },
            {
              "location" : "Location B",
              "quantity" : 200
            },
            {
              "location" : "Location C",
              "quantity" : 140
            },
            {
              "location" : "Location D",
              "quantity" : 123
            }
          ]
        }
      }
    ]
  }
}

Painless Elasticsearch Script for Filtering Nested Array Objects

3 Answers3