CODEWITHSUNDEEP
elasticsearch-5
user1393503
user1393503

Reputation: 285

Elastic search terms query for an AND condition for 2 properties dependent on each other

So I have a query to get records and the filter condition is something like this 

 GET  tenantforsneha55/permits/_search/ 
    {
      "from":0,
      "size":10,
      "sort":[
        {
          "permitNumber.keyword":{
            "order":"asc"
          }
        }
      ],
      "query":{
        "bool":{
          "must":[
            {
              "terms":{
                "workClassId":[
                  "1",
                  "2"
                ]
              }
            },
            {
              "terms":{
                "typeId":[
                  "1",
                  "2"
                ]
              }
            }
          ]
        }
      }
    }

This shows results for a filter like this Get records where typeId in ["1","2"] and classId in ["1","2"]

But I want the filter condition to be like this typeId = 1 and classId = 1 OR typeId = 2 and classId = 2.

Is there any way to have this ? I am using NEST,, this query is generated from that,will be great if you can give me the code in C#, Elastic v 5.5

Upvotes: 6

Views: 7992

Answers (3)

Sumit Maingi
Sumit Maingi

Reputation: 2263

Try the should operator in conjunction with must:

GET  tenantforsneha55/permits/_search/ 
    {
      "from":0,
      "size":10,
      "sort":[
        {
          "permitNumber.keyword":{
            "order":"asc"
          }
        }
      ],
      "query":{
        "bool":{
          "should":[
            {
              "match":{
                "workClassId": "1",
                "typeId": "1"
              }
            },
            {
              "match":{
                "workClassId": "2",
                "typeId": "2"
              }
            }
          ]
        }
      }
    }

I wrote it in a notepad, so excuse any syntax issues.

Elastic Search documentation for the same: https://www.elastic.co/guide/en/elasticsearch/reference/current/_executing_searches.html

Upvotes: 1

mongesh madhavan
mongesh madhavan

Reputation: 609

Read this for nested searching https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html

You Would have to use Nested Property here. And queries with Nest would be like mustFilters.Add(j => j.Nested(k => k.Path("Terms").Query(qj => qj.Bool(p => p.Must(queries)))));

https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html

Upvotes: 0

maximus ツ
maximus ツ

Reputation: 8065

You can use nested should and must queries like below,

   {
      "from":0,
      "size":10,
      "sort":[
        {
          "permitNumber.keyword":{
            "order":"asc"
          }
        }
      ],
      "query":{
        "bool":{
          "should":[
            {
              "bool": {
                "must": [
                  {
                    "term":{ "workClassId":"1" }
                  },
                  {
                    "term":{ "typeId":"1" }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "term":{ "workClassId":"2" }
                  },
                  {
                    "term":{ "typeId":"2" }
                  }
                ]
              }
            }
          ]
        }
      }
    }

This isn't simplest way but should do. Read more on this at https://www.elastic.co/blog/lost-in-translation-boolean-operations-and-filters-in-the-bool-query

You can also use filters in similar way. Check https://www.elastic.co/guide/en/elasticsearch/guide/current/combining-filters.html for more details.

Upvotes: 6

Related Questions