Search on multiple fields with ElasticSearch with or, and operators

Question

i`m need elasticsearch query equivalent from MySql. My sql query:

SELECT DISTINCT t.product_id AS id 
FROM tbl_sup_price t 
    INNER JOIN product a ON t.product_id = a.product_id 
    INNER JOIN a_product_to_category a2 ON a.product_id = a2.product_id 
    INNER JOIN a_category_description a3 ON a2.category_id = a3.category_id 
    INNER JOIN tbl_sup t2 ON t.sup_id = t2.id AND (t2.status = 1) 
WHERE ((t.quantity - t.reserved) > 0 AND t.is_active = 1) 
    AND (a.model_name LIKE '%word1%' OR t.name LIKE '%word1%') 
    AND (a.model_name LIKE '%word2%' OR t.name LIKE '%word2%') 
ORDER BY a.price ASC

I tried a lot of options, but nothing like what I need is not found, can someone tell me how to solve this problem?

As example:

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "and": [
          {
            "or": [
              {
                "fuzzy": {
                  "model_name": "nokian"
                }
              },
              {
                "fuzzy": {
                  "product_name": "nokian"
                }
              }
            ]
          },
          {
            "or": [
              {
                "text": {
                  "model_name": "195"
                }
              },
              {
                "text": {
                  "product_name": "195"
                }
              }
            ]
          }
        ]
      }
    }
  }
}

But if i run this query, i`m have error:

Parse Failure [Failed to parse source [{"query":{"filtered":{"query":{"match_all":{}},"filter":{"and":[{"or":[{"fuzzy":{"model_name":"nokian"}},{"fuzzy":{"product_name":"nokian"}}]},{"or":[{"text":{"model_name":"195"}},{"text":{"product_name":"195"}}]}]}}}}]]]; nested: QueryParsingException[[index] No filter registered for [fuzzy]]; }]

If word is numeric - search type must be text, else - fuzzy

fuzzy is a query, not a filter. check the documentation.

Rotem Hermon
– Rotem Hermon

2013-10-28 12:37:28 +00:00
Commented Oct 28, 2013 at 12:37 — Rotem Hermon
– Rotem Hermon, Commented Oct 28, 2013 at 12:37

i.paramonov · Accepted Answer · 2013-10-29 08:20:20Z

I`m solve this problem. In ElasticSearch there is such a great feature as function_score:

{
  "query": {
    "bool": {
      "must": [
        {
          "function_score": {
            "query": {
              "bool": {
                "should": [
                  {
                    "fuzzy": {
                      "model_name": "word1"
                    }
                  },
                  {
                    "fuzzy": {
                      "product_name": "word1"
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "function_score": {
            "query": {
              "bool": {
                "should": [
                  {
                    "fuzzy": {
                      "model_name": "word2"
                    }
                  },
                  {
                    "fuzzy": {
                      "product_name": "word2"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Collectives™ on Stack Overflow

Search on multiple fields with ElasticSearch with or, and operators

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related