# Issue while creating complex query

**URL:** <https://discuss.elastic.co/t/issue-while-creating-complex-query/86192>\
**Category:** Elasticsearch\
**Created:** [May 18, 2017, 4:17am UTC](https://discuss.elastic.co/t/issue-while-creating-complex-query/86192 "2017-05-18T04:17:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gautam\_Vanani](https://avatars.discourse-cdn.com/v4/letter/g/a9a28c/32.png) [@Gautam\_Vanani](https://discuss.elastic.co/u/Gautam_Vanani)\
**Post date:** [May 18, 2017, 4:17am UTC](https://discuss.elastic.co/t/issue-while-creating-complex-query/86192/1 "2017-05-18T04:17:29Z")

</div>

If state of an object is 3 then the object should not be shown but if the state of object is 3 and a particular sell\_code=xyz then the object should be returned, also it is not necessary that state of object is 3 it could be 1 or 2 also but the object should be returned only if **state of object is 3** and **sell\_code is xyz.**

How to create query for this condition, Can anybody help me in this?

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [May 18, 2017, 5:15pm UTC](https://discuss.elastic.co/t/issue-while-creating-complex-query/86192/2 "2017-05-18T17:15:21Z")

</div>

I'm not entirely clear on your requirements (confused by the wording). If you can formulate that into a boolean expression, it'll be relatively easy to convert into a query.

Do you just want the document returned if `state:3 AND sell_code:xyz`?

If yes, that's pretty simple:

```auto
{
  "query": {
    "bool": {
      "filter": [
        { "term" : { "state" : 3 } },
        { "term" : { "sell_code" : "xyz" } },
      ]
    }
  }
]

```

---

<div class="post-metadata">

**Author:** ![Gautam\_Vanani](https://avatars.discourse-cdn.com/v4/letter/g/a9a28c/32.png) [@Gautam\_Vanani](https://discuss.elastic.co/u/Gautam_Vanani)\
**Post date:** [May 20, 2017, 10:48pm UTC](https://discuss.elastic.co/t/issue-while-creating-complex-query/86192/3 "2017-05-20T22:48:41Z")

</div>

Hi @polyfractal,

(shape = 1) &&  
(color = 1) &&  
( (state != 0) || (state != 6) || ( (state = 3) && (reason\_code = 1) ) )

---

<div class="post-metadata">

**Author:** ![Gautam\_Vanani](https://avatars.discourse-cdn.com/v4/letter/g/a9a28c/32.png) [@Gautam\_Vanani](https://discuss.elastic.co/u/Gautam_Vanani)\
**Post date:** [May 20, 2017, 11:36pm UTC](https://discuss.elastic.co/t/issue-while-creating-complex-query/86192/4 "2017-05-20T23:36:03Z")

</div>

Hi @polyfractal,  
thank you for asking boolean expression,  
below query is working fine where I changed the condition like,  
!(state=3&&reason\_code=0)

```
{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "terms": {
                "shape": [
                  1
                ]
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "bool": {
                      "must_not": [
                        {
                          "terms": {
                            "stone_state": [
                              0,
                              6
                            ]
                          }
                        },
                        {
                          "bool": {
                            "must": [
                              {
                                "match": {
                                  "stone_state": 3
                                }
                              },
                              {
                                "match": {
                                  "reason_code": 0
                                }
                              }
                            ]
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

```

and if condition is [quote="Gautam\_Vanani, post:3, topic:86192"]  
( (state != 0) || (state != 6) || ( (state = 3) && (reason\_code = 1) ) )  
[/quote]

```
{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [{
              "terms": {
                "shape":[1,2]
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "bool": {
                      "must_not": [
                        {
                          "terms": {
                            "state": [
                              0,
                              6
                            ]
                          }
                        },
                  {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "state": 3
                          }
                        },
                        {
                          "match": {
                            "reason_code": 1
                          }
                        }
                      ]
                    }
                  }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

```

than is returning data of state=3 and reason code is 0 as well  
Can you help me in this?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 17, 2017, 11:36pm UTC](https://discuss.elastic.co/t/issue-while-creating-complex-query/86192/5 "2017-06-17T23:36:03Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.