should nested within must #51

gitemconte · 2016-08-25T13:21:16Z

I am curious if it is possible with this module to set up a complex elastic query that has should queries nested within a must.

Simply, I am trying to recreate this SQL query:
SELECT * FROM test where status = 'active' and (city = 'New York' or city = 'Toronto')

I would like to get back something similar to the below, but I have only been able to have the MUST and the SHOULD on the same level. Is this functionality possible? It seems like a simple query case, but maybe I am wrong.

{
    "query": {
        "bool": {
            "must": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "status": {
                                    "query": "active"
                                }
                            }
                        },
                        {
                            "bool": {
                                "should": [
                                    {
                                        "match": {
                                            "city": {
                                                "query": "New York"
                                            }
                                        }
                                    },
                                    {
                                        "match": {
                                            "city": {
                                                "query": "Toronto"
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        }
    }
}

The text was updated successfully, but these errors were encountered:

danpaz · 2016-08-25T14:56:12Z

Nested queries haven't been developed yet, but I think it is possible to add this. #16 is a good example of how this was done for nesting aggregations and I think a similar approach could work here.

jhn134910 · 2017-01-20T12:46:03Z

I may have misinterpreted the question but is the question about supporting nested bool structure rather than a nested query accessing a nested object? By the way -- really love this project.

chiragob · 2017-02-23T14:58:18Z

is there a way to build has_child with "or" in bodybuilder

{
"query":
{
"bool":
{
"should": [
{
"has_child": {
"inner_hits":{},
"type": "emp",
"query": {
"match": {
"name": "xyz"
}
}
}
},
{
"bool":
{
"must":
[
{
"match" : { "name" : "xyz" }
},
{
"match" : { "_type" : "emps" }
}
],
"must_not": [
{
"has_child": {
"type": "emp",
"query": {
"exists":
{
"field": "name"
}
}
}
}
]
}
}
]
}
}
}

diegoprd · 2017-04-11T18:05:47Z

@gitemconte I got into the same issue... and yes, I guess we should create a PR for this soon... but for now, as a workaround, I am manually doing it by using the getQuery() function

So, you could do something like this:

const body = bodybuilder()
    .query(...)
    .agg(...)

const innerBoolQueryForTheMustClause = bodybuilder()
   .query(...)

body.getQuery().bool.must.push(innerBoolQueryForTheMustClause.getQuery())

I know is not nice... but as a workaround, for now, it let me do the query I needed...

NOTE: There is a little edge case in the case of not having a must clause yet... so you can easily add the must close by adding a couple of .query('match_all') to the main body...

const body = bodybuilder()
    .query('match_all')
    .query('match_all')

const innerBoolQueryForTheMustClause = bodybuilder()
   .query(...)

body.getQuery().bool.must.push(innerBoolQueryForTheMustClause.getQuery())

Again, I know is not a nice approach but it will do the its job for now...
hope that helps.

sudo-suhas · 2017-04-25T02:53:03Z

I have been working on a library for building elasticsearch DSL heavily inspired by elastic.js which unfortunately was abandoned.

The requirement, SELECT * FROM test where status = 'active' and (city = 'New York' or city = 'Toronto') can be achieved like so:

const bob = require('elastic-builder');

const boolQry = bob
    .boolQuery()
    .must(bob.matchQuery('status', 'active'))
    .must(
        bob.boolQuery()
            .should(bob.matchQuery('city', 'New York'))
            .should(bob.matchQuery('city', 'Toronto'))
            .minimumShouldMatch(1)
    )
    .toJSON();

Link: http://github.com/sudo-suhas/elastic-builder

Hope this helps 😄

gophry · 2017-04-27T15:05:07Z

@gitemconte

I tried on a similar requirement: works fine.. on ElasticSearch 2.4 and 5.2

bodybuilder()
    .query('match','status','active')
	.orQuery('match','city','New York')
    .orQuery('match','city','Toronto')
.build()

caioflores · 2017-05-02T18:28:12Z

@gitemconte I made a approach similar to @diegoprd one, I needed something like this in my filters:

  "filter": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "term": {"Field": "Value"}
              },
              {
                "term": {"Field": "Value2"}
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {"Field2": "Value3"}
              },
              {
                "term": {"Field2": "Value4"}
              }
            ]
          }
        }
      ]
    }
  }

To get this result dinamically with bodybuilder I did:

   const query = bodybuilder().query('match_all');

   // Did this to create the must
   query.filter('term', 'Field', 'temporary');
   query.filter('term', 'Field', 'temporary');
   query.getFilter().bool.must.length = 0;
   
  // For each one of my 'shoulds' I created a filter of bodybuilder type
  // and applied the 'orFilter', than I inserted it in the main query
  Object.keys(filters).forEach((key) => {
      const filter = bodybuilder();
       for (let i = 0; i < filters[key].length; i += 1) {
         const value = filters[key][i];
         filter.orFilter('term', key, value);
       }
       query.getFilter().bool.must.push(filter.getFilter());
   });

Obviously this is not a good solution but it works, and I hope it helps someone, if you have any question just ask.

I started using bodybuilder and my project needs many nested queries, @danpaz we can create a PR for this? How can we help?

danpaz · 2017-05-03T22:44:40Z

I'd be happy to look at a PR to address this as I see it's caused confusion for folks. I just haven't had time myself to dig into it, and as @gophry points out sometimes the additional nesting isn't exactly needed. Your query may be different, though, not sure. In any case there seems to be a need for this feature.

Btw you may be able to use the undocumented getFilter method to improve your workaround. Take a look at the source code for that, let me know if you have any questions about it.

caioflores · 2017-05-04T14:51:44Z

@danpaz thanks for the answer. I'll take a look at getFilter and try @gophry approach to see if I can handle it in a better way.

johannes-scharlach · 2017-08-12T18:38:24Z

As of #134 it is possible to do nested filters, so I'm closing this issue.

As described in #142, this is not possible for nested queries right now. A possible solution that breaks compatability to elasticsearch version 1 is presented in #144.

dongyunqi2018 · 2019-09-05T10:06:07Z

it works in this way:

curl -X GET localhost:9200/transfer_trance/_search?pretty -H "Content-Type:application/json" -d'
{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "is_del": 1
                    }
                },
                {
                    "bool": {
                        "should": [
                            {
                                "term": {
                                    "monitor_url.keyword": "http://tx-g.xxxxxx.com/d/INzdsdYnu?idfa=__IDFA__&os=__OS__&timestamp=__TS__&callback=__CALLBACK_URL__"
                                }
                            },
                            {
                                "term": {
                                    "download_url.keyword": "https://count.xxxxxxx.com/d.php?id=738896&urlos=android&from_type=web&time=1733"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    },
    "size": 1,
    "sort": [
        {
            "created_at": "desc"
        }
    ]
}
'; echo

prasher-ayce · 2020-06-15T06:46:21Z

it works in this way:

curl -X GET localhost:9200/transfer_trance/_search?pretty -H "Content-Type:application/json" -d'
{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "is_del": 1
                    }
                },
                {
                    "bool": {
                        "should": [
                            {
                                "term": {
                                    "monitor_url.keyword": "http://tx-g.xxxxxx.com/d/INzdsdYnu?idfa=__IDFA__&os=__OS__&timestamp=__TS__&callback=__CALLBACK_URL__"
                                }
                            },
                            {
                                "term": {
                                    "download_url.keyword": "https://count.xxxxxxx.com/d.php?id=738896&urlos=android&from_type=web&time=1733"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    },
    "size": 1,
    "sort": [
        {
            "created_at": "desc"
        }
    ]
}
'; echo

@dongyunqi2018 I am facing a similar issue your solution is not working for me,
My required results should work like this
where com_ref= "something" AND( fr_id = 123 OR fr_id =456).
Here is what i am trying but not getting any results
{ "query": { "bool": { "filter": { "bool": { "must": [ { "match_phrase": { "com_ref": "ddfdfdfdf" } }, { "bool": { "should": [ { "term": { "fr_id ": 511168 } }, { "term": { "fr_id ": 511258 } } ] } } ] } } } } }

ferronrsmith · 2020-06-15T18:58:27Z

bodybuilder()
        .query('term', 'is_del', 1)
		.query('bool', b => 
               b.orQuery('term', 'monitor_url.keyword', 'http://tx-g.xxxxxx.com/d/INzdsdYnu?idfa=__IDFA__&os=__OS__&timestamp=__TS__&callback=__CALLBACK_URL__')
  			   .orQuery('term', 'download_url.keyword', 'https://count.xxxxxxx.com/d.php?id=738896&urlos=android&from_type=web&time=1733'))  
  .size(1)
  .sort([{
            "created_at": "desc"
        }])
  .build()

prasher-ayce · 2020-06-15T19:06:49Z

bodybuilder()
        .query('term', 'is_del', 1)
		.query('bool', b => 
               b.orQuery('term', 'monitor_url.keyword', 'http://tx-g.xxxxxx.com/d/INzdsdYnu?idfa=__IDFA__&os=__OS__&timestamp=__TS__&callback=__CALLBACK_URL__')
  			   .orQuery('term', 'download_url.keyword', 'https://count.xxxxxxx.com/d.php?id=738896&urlos=android&from_type=web&time=1733'))  
  .size(1)
  .sort([{
            "created_at": "desc"
        }])
  .build()

I am using this with filter query like this ` bodybuilder()
.filter('match_phrase', 'com_ref', "de3e75da-8ac6-40bc-8586-f4b2e1e01383")
.filter('bool', b => b
.orFilter('term', 'fr_id ', 511168)
.orFilter('term', 'fr_id ', 511258)

)
.build()`

Is this making a valid Elastic search query if yes should i delete my Index and recreate it ?

ferronrsmith · 2020-06-18T18:23:58Z

Query looks fine, what's the issue you're having ? Also what's the mapping for the index ?

prasher-ayce · 2020-06-19T13:18:05Z

having

"fr_id" : {
      "type" : "long"
    },

    "com_ref" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },

I should be getting results for where com_ref ="some value" and (fr_id = 1 or fr_id =2) right?
I am not getting any results

ferronrsmith · 2020-06-19T14:49:43Z

try removing the match_phrase portion

bodybuilder()
.orFilter('term', 'fr_id ', 511168)
.orFilter('term', 'fr_id ', 511258).build()

What do you get ?

Also what's in com_def ? is it GUIDs ?

prasher-ayce · 2020-06-19T14:56:18Z

bodybuilder()
.orFilter('term', 'fr_id ', 511168)
.orFilter('term', 'fr_id ', 511258).build()

Yes com_ref is a Guid.
still no results.
{ "took" : 3, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 0, "relation" : "eq" }, "max_score" : null, "hits" : [ ] } }

ferronrsmith · 2020-06-19T14:58:17Z

The only way there wouldn't be any results, if no data exist with those fr_id. Can you share a dump of the data ? I'd be happy to help out, if you want.

prasher-ayce · 2020-06-19T15:07:43Z

The only way there wouldn't be any results, if no data exist with those fr_id. Can you share a dump of the data ? I'd be happy to help out, if you want.

Attaching a file of the dump with data only for fr_id and com_ref
As you can see data is there fir fr_id thats why i am pulling my hairs
dump122.txt

ferronrsmith · 2020-06-19T16:19:41Z

I just realised, there is an empty string at the end

bodybuilder()
.orFilter('term', 'fr_id', 511168)
.orFilter('term', 'fr_id', 511258).build()

The above worked for me

results :

{
	"took": 5,
	"timed_out": false,
	"_shards": {
		"total": 5,
		"successful": 5,
		"skipped": 0,
		"failed": 0
	},
	"hits": {
		"total": 2,
		"max_score": 0,
		"hits": [{
				"_index": "client_basic_detail",
				"_type": "_doc",
				"_id": "4efffaf1-ffed-4ac0-ad59-a60914312dbf",
				"_score": 0,
				"_source": {
					"com_ref": "de3e75da-8ac6-40bc-8586-f4b2e1e01383",
					"fr_id": 511168
				}
			},
			{
				"_index": "client_basic_detail",
				"_type": "_doc",
				"_id": "bbe5b6c3-8ba8-40ea-9569-2852b2539a80",
				"_score": 0,
				"_source": {
					"com_ref": "de3e75da-8ac6-40bc-8586-f4b2e1e01383",
					"fr_id": 511258
				}
			}
		]
	}
}

ferronrsmith · 2020-06-19T16:23:07Z

And then for the above query

bodybuilder()
.filter('term', 'com_ref.keyword', "de3e75da-8ac6-40bc-8586-f4b2e1e01383")
.filter('bool', b => b
.orFilter('term', 'fr_id', 511168)
.orFilter('term', 'fr_id', 511258)).build()

==>

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "com_ref.keyword": "de3e75da-8ac6-40bc-8586-f4b2e1e01383"
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "term": {
                      "fr_id": 511168
                    }
                  },
                  {
                    "term": {
                      "fr_id": 511258
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

==>

{
	"took": 24,
	"timed_out": false,
	"_shards": {
		"total": 5,
		"successful": 5,
		"skipped": 0,
		"failed": 0
	},
	"hits": {
		"total": 2,
		"max_score": 0,
		"hits": [{
				"_index": "client_basic_detail",
				"_type": "_doc",
				"_id": "4efffaf1-ffed-4ac0-ad59-a60914312dbf",
				"_score": 0,
				"_source": {
					"com_ref": "de3e75da-8ac6-40bc-8586-f4b2e1e01383",
					"fr_id": 511168
				}
			},
			{
				"_index": "client_basic_detail",
				"_type": "_doc",
				"_id": "bbe5b6c3-8ba8-40ea-9569-2852b2539a80",
				"_score": 0,
				"_source": {
					"com_ref": "de3e75da-8ac6-40bc-8586-f4b2e1e01383",
					"fr_id": 511258
				}
			}
		]
	}
}

prasher-ayce · 2020-06-19T16:24:53Z

And then for the above query

bodybuilder()
.filter('term', 'com_ref.keyword', "de3e75da-8ac6-40bc-8586-f4b2e1e01383")
.filter('bool', b => b
.orFilter('term', 'fr_id', 511168)
.orFilter('term', 'fr_id', 511258)).build()

==>

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "com_ref.keyword": "de3e75da-8ac6-40bc-8586-f4b2e1e01383"
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "term": {
                      "fr_id": 511168
                    }
                  },
                  {
                    "term": {
                      "fr_id": 511258
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

==>

{
	"took": 24,
	"timed_out": false,
	"_shards": {
		"total": 5,
		"successful": 5,
		"skipped": 0,
		"failed": 0
	},
	"hits": {
		"total": 2,
		"max_score": 0,
		"hits": [{
				"_index": "client_basic_detail",
				"_type": "_doc",
				"_id": "4efffaf1-ffed-4ac0-ad59-a60914312dbf",
				"_score": 0,
				"_source": {
					"com_ref": "de3e75da-8ac6-40bc-8586-f4b2e1e01383",
					"fr_id": 511168
				}
			},
			{
				"_index": "client_basic_detail",
				"_type": "_doc",
				"_id": "bbe5b6c3-8ba8-40ea-9569-2852b2539a80",
				"_score": 0,
				"_source": {
					"com_ref": "de3e75da-8ac6-40bc-8586-f4b2e1e01383",
					"fr_id": 511258
				}
			}
		]
	}
}

Oh Man i feel so stupid right now . i was pulling my hairs for this .
Thank you, thank you very much

ferronrsmith · 2020-06-19T16:34:12Z

@KillerAyce no worries, well I overlooked too. Glad we figured out, all the best !

danpaz added the enhancement label Aug 25, 2016

danpaz mentioned this issue Jan 24, 2017

Q: Is there a way to do an "or" filter? #104

Closed

johannes-scharlach closed this as completed Aug 12, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

should nested within must #51

should nested within must #51

gitemconte commented Aug 25, 2016

danpaz commented Aug 25, 2016

jhn134910 commented Jan 20, 2017 •

edited

Loading

chiragob commented Feb 23, 2017

diegoprd commented Apr 11, 2017

sudo-suhas commented Apr 25, 2017

gophry commented Apr 27, 2017

caioflores commented May 2, 2017 •

edited

Loading

danpaz commented May 3, 2017

caioflores commented May 4, 2017

johannes-scharlach commented Aug 12, 2017

dongyunqi2018 commented Sep 5, 2019

prasher-ayce commented Jun 15, 2020

ferronrsmith commented Jun 15, 2020

prasher-ayce commented Jun 15, 2020

ferronrsmith commented Jun 18, 2020 •

edited

Loading

prasher-ayce commented Jun 19, 2020

ferronrsmith commented Jun 19, 2020

prasher-ayce commented Jun 19, 2020

ferronrsmith commented Jun 19, 2020

prasher-ayce commented Jun 19, 2020 •

edited

Loading

ferronrsmith commented Jun 19, 2020 •

edited

Loading

ferronrsmith commented Jun 19, 2020

prasher-ayce commented Jun 19, 2020

ferronrsmith commented Jun 19, 2020

should nested within must #51

should nested within must #51

Comments

gitemconte commented Aug 25, 2016

danpaz commented Aug 25, 2016

jhn134910 commented Jan 20, 2017 • edited Loading

chiragob commented Feb 23, 2017

diegoprd commented Apr 11, 2017

sudo-suhas commented Apr 25, 2017

gophry commented Apr 27, 2017

caioflores commented May 2, 2017 • edited Loading

danpaz commented May 3, 2017

caioflores commented May 4, 2017

johannes-scharlach commented Aug 12, 2017

dongyunqi2018 commented Sep 5, 2019

prasher-ayce commented Jun 15, 2020

ferronrsmith commented Jun 15, 2020

prasher-ayce commented Jun 15, 2020

ferronrsmith commented Jun 18, 2020 • edited Loading

prasher-ayce commented Jun 19, 2020

ferronrsmith commented Jun 19, 2020

prasher-ayce commented Jun 19, 2020

ferronrsmith commented Jun 19, 2020

prasher-ayce commented Jun 19, 2020 • edited Loading

ferronrsmith commented Jun 19, 2020 • edited Loading

ferronrsmith commented Jun 19, 2020

prasher-ayce commented Jun 19, 2020

ferronrsmith commented Jun 19, 2020

jhn134910 commented Jan 20, 2017 •

edited

Loading

caioflores commented May 2, 2017 •

edited

Loading

ferronrsmith commented Jun 18, 2020 •

edited

Loading

prasher-ayce commented Jun 19, 2020 •

edited

Loading

ferronrsmith commented Jun 19, 2020 •

edited

Loading