How to use multi where condition with elasticsearch

Question

I'm using laravel + elasticsearch. I have an array like this:

[
    {
        "title": "product_title",
        "stocks": [
            {
                "country": "EN",
                "stock": 0
            },
            {
                "country": "IN",
                "stock": 1
            }
        ]
    },
    {
        "title": "product_title_2",
        "stocks": [
            {
                "country": "EN",
                "stock": 1
            },
            {
                "country": "IN",
                "stock": 0
            }
        ]
    }
]

Now I want to find all objects has country equal EN and stock is greater than 1.

updated

my query:

{
    "index": "products",
    "body": {
        "size": 15,
        "from": 1,
        "sort": [
            {
                "stock": {
                    "order": "desc"
                }
            }
        ],
        "query": {
            "bool": {
                "must": [
                    {
                        "query_string": {
                            "query": "**",
                            "type": "best_fields",
                            "fields": [
                                "erp_id",
                                "title_en^2",
                                "translations.title^2",
                                "erp.title_en",
                                "erp.title",
                                "erp.options.title",
                                "erp.options.title_en"
                            ],
                            "analyze_wildcard": true,
                            "allow_leading_wildcard": true
                        }
                    }
                ],
                "filter": [
                    {
                        "term": {
                            "is_active": 1
                        }
                    },
                    {
                        "term": {
                            "shops.shop_id": 1
                        }
                    }
                ]
            }
        },
        "aggs": {
            "max_price": {
                "filter": {
                    "term": {
                        "erp.price_lists.currency.abbr": "tmn"
                    }
                },
                "aggs": {
                    "result": {
                        "max": {
                            "field": "erp.price_lists.pivot.price_tt"
                        }
                    }
                }
            },
            "min_price": {
                "filter": {
                    "term": {
                        "erp.price_lists.currency.abbr": "tmn"
                    }
                },
                "aggs": {
                    "result": {
                        "min": {
                            "field": "erp.price_lists.pivot.price_tt"
                        }
                    }
                }
            }
        }
    }
}

Are you looking the expression to search this in Elastcsearch or in PHP? — enriqueojedalara
– enriqueojedalara, Commented Apr 28, 2021 at 5:53

Bhavya · Accepted Answer · 2021-04-28 06:00:36Z

2

You can use nested query along with inner_hits to get the object satisfying the requirements

Adding a working example

Index Mapping:

{
  "mappings": {
    "properties": {
      "stocks": {
        "type": "nested"
      }
    }
  }
}

Index Data:

{
  "title": "product_title_2",
  "stocks": [
    {
      "country": "EN",
      "stock": 1
    },
    {
      "country": "IN",
      "stock": 0
    }
  ]
}
{
  "title": "product_title",
  "stocks": [
    {
      "country": "EN",
      "stock": 0
    },
    {
      "country": "IN",
      "stock": 1
    }
  ]
}
{
  "title": "product_title_3",
  "stocks": [
    {
      "country": "EN",
      "stock": 2
    },
    {
      "country": "IN",
      "stock": 0
    }
  ]
}

Search Query:

    {
  "query": {
    "nested": {
      "path": "stocks",
      "query": {
        "bool": {
          "filter": [
            {
              "match": {
                "stocks.country": "EN"
              }
            },
            {
              "range": {
                "stocks.stock": {
                  "gt": 1
                }
              }
            }
          ]
        }
      },
      "inner_hits":{}
    }
  }
}

Search Result:

"hits": [
  {
    "_index": "67294405",
    "_type": "_doc",
    "_id": "3",
    "_score": 0.0,
    "_source": {
      "title": "product_title_3",
      "stocks": [
        {
          "country": "EN",
          "stock": 2
        },
        {
          "country": "IN",
          "stock": 0
        }
      ]
    },
    "inner_hits": {
      "stocks": {
        "hits": {
          "total": {
            "value": 1,
            "relation": "eq"
          },
          "max_score": 0.0,
          "hits": [
            {
              "_index": "67294405",
              "_type": "_doc",
              "_id": "3",
              "_nested": {
                "field": "stocks",
                "offset": 0
              },
              "_score": 0.0,
              "_source": {
                "country": "EN",
                "stock": 2
              }
            }
          ]
        }
      }
    }
  }
]

edited Apr 28, 2021 at 6:00

answered Apr 28, 2021 at 5:53

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

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

4 Comments

Val Over a year ago

Consider using bool/filter instead of bool/must for exact matches

Val Over a year ago

Also there's no inner_hits section in your query

apokryfos Over a year ago

The difference between must and filter is that filter does not affect the score, while must does so it's not necessarily about exact matches. In this case you might (or might not) want items that match both to score higher than items that match one of the two conditions. Since that's unclear from the question I think must and filter would be equally valid

S.M_Emamian Over a year ago

@ESCoder updated my question. added my query.

Collectives™ on Stack Overflow

How to use multi where condition with elasticsearch

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related