Group Conditions

[TomTom]

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));

[Mark_Harwood]

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"
								}
							}]
						}
					}]
				}
			}]
		}
	}]
}

[TomTom]

Thank you very much, this will help a lot.

[Andrei_Stefan]

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.

In short you write something like this:

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:

{
    "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
        }
    }
}

[TomTom]

This function of the X-Pack is incredible.

Is the X-Pack a paid resource?

[dadoonet (David Pilato)]

While the code for X-Pack is available in a public repository, we do not claim that it is "Open Source". See 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.

[TomTom]

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

[TomTom]

The Lucene query syntax support search inside nested objects?

[Mark_Harwood]

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

[TomTom]

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 '!='?

[Mark_Harwood]

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

[TomTom]

Thanks.