# Group Conditions

**URL:** <https://discuss.elastic.co/t/group-conditions/149824>\
**Category:** Elasticsearch\
**Created:** [September 25, 2018, 12:38pm UTC](https://discuss.elastic.co/t/group-conditions/149824 "2018-09-25T12:38:32Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [September 25, 2018, 12:38pm UTC](https://discuss.elastic.co/t/group-conditions/149824/1 "2018-09-25T12:38:32Z")

</div>

Currently i have this Query to search data in ElasticSearch:

```
{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "account_id": 128
                    }
                },
                {
                    "term": {
                        "city_id": 256
                    }
                }
            ]
        }
    },
    "_source": [],
    "size": 10000,
    "sort": {
        "customer_id": {
            "order": "desc"
        }
    }
}

```

That equals this SQL:

`SELECT * FROM table WHERE account_id = 128 AND city_id = 256;`

My problem is that I do not know how to create groups with conditions, so my searches are always with AND / AND / AND condition, which ends up limiting the use of my data.

Is possible create a JSON to search data like this?

`SELECT * FROM table WHERE account_id = 128 AND city_id = 256 AND (neighborhood_id = 512 OR street_id = 1024 AND (customer_active = true OR customer_new = true));`

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 25, 2018, 1:13pm UTC](https://discuss.elastic.co/t/group-conditions/149824/2 "2018-09-25T13:13:27Z")

</div>

You could use the Lucene query syntax using the Query string query:

```
"query_string": {
	"query": "account_id:128 AND city_id:256 AND (neighborhood_id:512 OR street_id:1024 AND (customer_active:true OR customer_new:true))"
}

```

or the Boolean equivalent I think is this:

```
"bool": {
	"must": [{
		"match": {
			"account_id": "128"
		}
	}, {
		"match": {
			"city_id": "256"
		}
	}, {
		"bool": {
			"should": [{
				"match": {
					"neighborhood_id": "512"
				}
			}, {
				"bool": {
					"must": [{
						"match": {
							"street_id": "1024"
						}
					}, {
						"bool": {
							"should": [{
								"match": {
									"customer_active": "true"
								}
							}, {
								"match": {
									"customer_new": "true"
								}
							}]
						}
					}]
				}
			}]
		}
	}]
}
```

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [September 25, 2018, 1:16pm UTC](https://discuss.elastic.co/t/group-conditions/149824/3 "2018-09-25T13:16:12Z")

</div>

Thank you very much, this will help a lot.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [September 26, 2018, 5:34am UTC](https://discuss.elastic.co/t/group-conditions/149824/4 "2018-09-26T05:34:11Z")

</div>

You could, also, try the `translate` API from ES-SQL to give you an ES query given a certain SQL query :-). More about this API [here](https://www.elastic.co/guide/en/elasticsearch/reference/6.x/sql-translate.html).

In short you write something like this:

```auto
POST /_xpack/sql/translate
{
    "query": "SELECT * FROM table WHERE account_id = 128 AND city_id = 256 AND (neighborhood_id = 512 OR street_id = 1024 AND (customer_active = true OR customer_new = true))"
}

```

And the plugin will answer with something like this:

```auto
{
    "size": 1000,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "must": [
                            {
                                "term": {
                                    "account_id": {
                                        "value": 128,
                                        "boost": 1
                                    }
                                }
                            },
                            {
                                "term": {
                                    "city_id": {
                                        "value": 256,
                                        "boost": 1
                                    }
                                }
                            }
                        ],
                        "adjust_pure_negative": true,
                        "boost": 1
                    }
                },
                {
                    "bool": {
                        "should": [
                            {
                                "term": {
                                    "neighborhood_id": {
                                        "value": 512,
                                        "boost": 1
                                    }
                                }
                            },
                            {
                                "bool": {
                                    "must": [
                                        {
                                            "term": {
                                                "street_id": {
                                                    "value": 1024,
                                                    "boost": 1
                                                }
                                            }
                                        },
                                        {
                                            "bool": {
                                                "should": [
                                                    {
                                                        "term": {
                                                            "customer_active": {
                                                                "value": true,
                                                                "boost": 1
                                                            }
                                                        }
                                                    },
                                                    {
                                                        "term": {
                                                            "customer_new": {
                                                                "value": true,
                                                                "boost": 1
                                                            }
                                                        }
                                                    }
                                                ],
                                                "adjust_pure_negative": true,
                                                "boost": 1
                                            }
                                        }
                                    ],
                                    "adjust_pure_negative": true,
                                    "boost": 1
                                }
                            }
                        ],
                        "adjust_pure_negative": true,
                        "boost": 1
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1
        }
    }
}

```

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [September 26, 2018, 11:56am UTC](https://discuss.elastic.co/t/group-conditions/149824/5 "2018-09-26T11:56:57Z")

</div>

This function of the X-Pack is incredible.

Is the X-Pack a paid resource?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [September 26, 2018, 2:09pm UTC](https://discuss.elastic.co/t/group-conditions/149824/6 "2018-09-26T14:09:10Z")

</div>

While the code for X-Pack is available in a public repository, [we do not claim that it is "Open Source"](https://www.elastic.co/products/x-pack/open). See [Subscriptions](https://www.elastic.co/subscriptions) about the different features available.

The default distribution of Elasticsearch includes all of X-Pack, but the default license is "basic".

You can activate a free trial of the commercial features via Kibana (Management -\> License) or via [the API](https://www.elastic.co/guide/en/elastic-stack-overview/6.3/license-management.html).

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [September 27, 2018, 8:52pm UTC](https://discuss.elastic.co/t/group-conditions/149824/7 "2018-09-27T20:52:33Z")

</div>

Is possible use parameterized (Painless) scripts with Lucene Query Syntax inside search subgroups?

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [October 2, 2018, 12:52pm UTC](https://discuss.elastic.co/t/group-conditions/149824/8 "2018-10-02T12:52:32Z")

</div>

The Lucene query syntax support search inside nested objects?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [October 2, 2018, 12:59pm UTC](https://discuss.elastic.co/t/group-conditions/149824/9 "2018-10-02T12:59:04Z")

</div>

Nope. You;d need the elasticsearch query DSL (JSON) to do that currently

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [October 2, 2018, 1:03pm UTC](https://discuss.elastic.co/t/group-conditions/149824/10 "2018-10-02T13:03:06Z")

</div>

Thanks @Mark_Harwood.

Last question, viewing your example i noticed that the `match` command is used for `'='` condition, what would be the command for the condition `'!='`?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [October 2, 2018, 1:04pm UTC](https://discuss.elastic.co/t/group-conditions/149824/11 "2018-10-02T13:04:52Z")

</div>

Wrap a `match` query, nested inside the `must_not` part of a `bool` query

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [October 2, 2018, 1:05pm UTC](https://discuss.elastic.co/t/group-conditions/149824/12 "2018-10-02T13:05:39Z")

</div>

Thanks.

---

<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:** [October 30, 2018, 1:05pm UTC](https://discuss.elastic.co/t/group-conditions/149824/13 "2018-10-30T13:05:39Z")

</div>

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