Source filtering an array of objects in ElasticSearch

Question

Here is a document in ElasticSearch

"CompanyId": 5733,
"PartNumber": "W8S038",
"Name_en": "#8 Washer, M4 Compatible, Stainless Steel, Pack of 100",
"ProductId": 90023,
"CompanyName": "Washers Ltd",
"Prices": [

    {
        "BuyerId": 308,
        "Price": 2.42
    }
    ,
    {
        "BuyerId": 406,
        "Price": 2.22
    }
]
}

Obviously we can't let on to buyer 308 that buyer 406 is getting a better price. Therefore when buyer 308 is searching I need to remove all of the prices for other buyers.

I'd like to do this by using source filtering. But how?!

(I could exclude Prices and add back in the required price by using a script_field. However, that means that the price is not part of the source document and therefore ReactiveSearch can't see it and therefore can't sort on it.)

Update: here is the query generated by ReactiveSearch to which I need to append the limit on prices:

   "query":{
      "bool":{
         "must":[
            {
               "bool":{
                  "must":[
                     {
                        "bool":{
                           "must":[
                              {
                                 "bool":{
                                    "should":[
                                       {
                                          "multi_match":{
                                             "query":"m4 washer",
                                             "fields":[
                                                "Name_en"
                                             ],
                                             "type":"cross_fields",
                                             "operator":"and"
                                          }
                                       },
                                       {
                                          "multi_match":{
                                             "query":"m4 washer",
                                             "fields":[
                                                "Name_en"
                                             ],
                                             "type":"phrase_prefix",
                                             "operator":"and"
                                          }
                                       }
                                    ],
                                    "minimum_should_match":"1"
                                 }
                              }
                           ]
                        }
                     }
                  ]
               }
            }
         ],
"filter": [            
        {
          "nested": {
            "path": "Prices",
            "query": {
              "term": {
                "Prices.CompanyId": 1474
              }
            },
            "inner_hits": {}
          }
        }
      ]      
      }
   },
   "size":10,
   "aggs":{
      "CompanyName.raw":{
         "terms":{
            "field":"CompanyName.raw",
            "size":1000,
            "order":{
               "_count":"desc"
            }
         }
      }
   },
   "_source":{
      "excludes":[
         "PurchasingViews",
         "ContractFilters",
         "SearchField*",
         "Keywords*",
         "Menus*"
      ]
   },
   "from":0,
   "sort":[
      {
         "Name_en.raw":{
            "order":"asc"
         }
      }
   ],
   "script_fields":{
      "price":{
         "script":{
            "lang":"painless",
            "inline":"if(params['_source']['Prices'] != null){for(p in params['_source']['Prices']){ if(p.CompanyId == 1474) return p.Price; }} return null;"
         }
      }
   }
}

(That bool, must, bool, must, bool, must, bool, should seems rather stupid?)

How are you filtering price, using max aggregation?

jaspreet chahal
– jaspreet chahal

2019-09-24 12:00:29 +00:00
Commented Sep 24, 2019 at 12:00 — jaspreet chahal
– jaspreet chahal, Commented Sep 24, 2019 at 12:00

Val · Accepted Answer · 2019-09-25 11:15:55Z

2

You need to use the nested inner_hits feature like below.

{
  "_source": [
    "CompanyId", "PartNumber", "Name_en", "ProductId", "CompanyName"
  ],
  "query": {
    "bool": {
      "filter": [            
        {
          "nested": {
            "path": "Prices",
            "query": {
              "term": {
                "Prices.BuyerId": 308
              }
            },
            "inner_hits": {}
          }
        }
      ]
    }
  }
}

In the output you'll get exactly what you expect, namely all the root-level fields and the matching prices for the given buyer.

UPDATE:

Here is how I would rewrite your query:

{
  "query": {
    "bool": {
      "minimum_should_match": "1",
      "should": [
        {
          "multi_match": {
            "query": "m4 washer",
            "fields": [
              "Name_en"
            ],
            "type": "cross_fields",
            "operator": "and"
          }
        },
        {
          "multi_match": {
            "query": "m4 washer",
            "fields": [
              "Name_en"
            ],
            "type": "phrase_prefix",
            "operator": "and"
          }
        }
      ],
      "filter": [
        {
          "nested": {
            "path": "Prices",
            "query": {
              "term": {
                "Prices.CompanyId": 1474
              }
            },
            "inner_hits": {}
          }
        }
      ]
    }
  },
  "size": 10,
  "aggs": {
    "CompanyName.raw": {
      "terms": {
        "field": "CompanyName.raw",
        "size": 1000,
        "order": {
          "_count": "desc"
        }
      }
    }
  },
  "_source": {
    "excludes": [
      "PurchasingViews",
      "ContractFilters",
      "SearchField*",
      "Keywords*",
      "Menus*",
      "Prices"
    ]
  },
  "from": 0,
  "sort": [
    {
      "Name_en.raw": {
        "order": "asc"
      }
    }
  ],
  "script_fields": {
    "price": {
      "script": {
        "lang": "painless",
        "inline": "if(params['_source']['Prices'] != null){for(p in params['_source']['Prices']){ if(p.CompanyId == 1474) return p.Price; }} return null;"
      }
    }
  }
}

edited Sep 25, 2019 at 11:15

answered Sep 24, 2019 at 12:04

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Richard Barraclough Over a year ago

That works! Cool; thank you. Now, how do I append that to an existing query?

Val Over a year ago

Great! Update your question with the query you have.

Richard Barraclough Over a year ago

As far as I can tell if you take all the bool, must out and leave just bool, should then you get the same result. However, wherevever I put the filter it doesn't do anything.

Richard Barraclough Over a year ago

Oh, hang on, it's producing an inner_hits property in the results; not removing the unwanted prices from the document.

Val Over a year ago

In the inner_hits section you only get the prices for CompanyId 1474. See my updated answer

Collectives™ on Stack Overflow

Source filtering an array of objects in ElasticSearch

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related