How can I write "select * from users where username ='kuldeep' and (status = 'ac-CodePudding

I tried this query

{
  "query": {
    "bool" : {
      "must" : {
        "match" : { "username": "kuldeep" }
      },
      "should" : [
        { "term" : {"status": "inactive"} },
        { "term" : { "status": "active" } }
      ],
      "minimum_should_match" : 0
    }
  }
}

I am getting results using the above query, but it returns users with the username kuldeep and kuldeep singh.

    "took": 1,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 2,
            "relation": "eq"
        },
        "max_score": 8.914892,
        "hits": [{
                "_index": "searchusers",
                "_type": "_doc",
                "_id": "6f9b37b1-2934-4125-bbf9-875e61c60730",
                "_score": 8.914892,
                "_source": {
                    "status": "active",
                    "createdOn": 1637145140000,
                    "id": "6f9b37b1-2934-4125-bbf9-875e61c60730",
                    "agreementAccepted": false,
                    "userNumber": "0000000000001251",
                    "email": "",
                    "createdBy": "a91a9b69-7774-4e2e-8901-037045c4f5e0",
                    "username": "kuldeep"
                }
            },
            {
                "_index": "searchusers",
                "_type": "_doc",
                "_id": "1744d7ae-3454-4ad4-9ea9-591102eb0369",
                "_score": 6.8384924,
                "_source": {
                    "status": "active",
                    "createdOn": 1639458287000,
                    "id": "1744d7ae-3454-4ad4-9ea9-591102eb0369",
                    "agreementAccepted": false,
                    "userNumber": "0000000000002429",
                    "email": "",
                    "createdBy": "39057c58-3ed6-457d-a832-2f69a2472943",
                    "username": "kuldeep singh"
                }
            }
        ]
    }
}

As mentioned in elastic docs (https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html) minimum_should_match default value is 0 if it includes match or filter. When I changed minimum_should_match to 1 it returns no data.

    "took": 0,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 0,
            "relation": "eq"
        },
        "max_score": null,
        "hits": []
    }
}

Also when using minimum_should_match as 0 it seems the should query has no impact in result.

CodePudding user response：

For exact search instead of match you need to use the term query, also you need to use the term query on the keyword field not on the text field, so if you are using the default mapping of Elasticsearch, for every text field there is .keyword field, in this case you can simply change relevant part of your query to below:

{
    "query" : {
        "term" : {
            "username.keyword" : "kuldeep"
        }
    }
}