Elasticsearch query to match on one field but should filter results based on an other field-CodePudding

Looking for help formulating a query for the following use case. I have a text field and an integer field which is an ID. I need to search for matching text but expecting the response to contain results that match one of the IDs. As an example, I have two fields. One is product ID which is text. And an owner ID which is an integer. Owners must be allowed to view only those products that are owned by them. To add on, an owner can have multiple IDs.

Sample records in Elasticsearch:

{
  "product": "MKL89ADH12",
  "ownerId" : 98765
},
{
  "product": "POIUD780H",
  "ownerId" : 12345
},
{
  "product": "UJK87TG89",
  "ownerId" : 98765
},
{
  "product": "897596YHJ",
  "ownerId" : 98765
},
{
  "product": "LKGGN764HH",
  "ownerId" : 784512
}

If 98765 and 12345 belong to the same owner, they should be able to view first 4 products only. And no results should be returned if they search for LKGGN764HH .

I tried following query but it gives me no results.

{
  "size": 24,
  "query": {
    "bool": {
        "must":[{
          "match" : {
              "product": {
                "query": "MKL89ADH12"
              }
            }
        },
        {
          "match" : {
              "product": {
                "query": "LKGGN764HH"
              }
            }
        },
        {
          "terms": {
          "ownerId": [98765, 12345],
          "boost": 1.0
          }
        }
        ]
    }
  }
}

I am expecting the response to contain MKL89ADH12 because I am filtering by the ownerId. Looking for help formulating the right query for my use case.

CodePudding user response：

You need to filter on "ownerId": [98765, 12345] and then use "should" clause to return documents which match any of the text.

{
  "size": 24,
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "ownerId": [
              98765,
              12345
            ],
            "boost": 1
          }
        }
      ],
      "should": [
        {
          "match": {
            "product": {
              "query": "MKL89ADH12"
            }
          }
        },
        {
          "match": {
            "product": {
              "query": "LKGGN764HH"
            }
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

Above query will translate to

select * from index 
where  ownerid in ( 98765, 12345)
AND (product IN ("MKL89ADH12","LKGGN764HH))

while your works like

select * from index 
where  ownerid in ( 98765, 12345)
AND product = "MKL89ADH12"
AND product = "LKGGN764HH"