Bool.should.term.$field = $val not actually excluding documents

Elastic Stack Elasticsearch
1

The gist below is a solid example of my "problem", I've tested this gist and it does reproduce the "problem". (Problem is in quotes because I'm probably just not doing the DSL right...)

Gist: https://gist.github.com/josiahbryan/df828752aef7147da979828784e21bcc

To paraphrase, I have documents like:
{ officeid: 4, _stringified: "foobar" }
{ officeid: 2, _stringified: "foobar" }
{ officeid: null, _stringified: "foobar" }
{ officeid: 0, _stringified: "foobar" }

And basically I want to match
(_stringified="foobar") AND (officeid=4 OR officeid=NULL OR officeid=0)

But when I do a _search that I THINK says that, it gives me everything matching "foobar" and doesn't even pretend like I said anything about officeid.

Here's the search DSL. (multi_match edited for readability, full multi_match in the gist.)

GET /my_index/_search
{
	"_source": ["_stringified","officeid"],
	"query": {
		"bool": {
			"should": [
				{
					"term": {
						"officeid": "4"
					}
				},
				{
					"bool": {
						"must_not": {
							"exists": {
								"field": "officeid"
							}
						}
					}
				}
			
			],

			"must":[
				{
					"multi_match": {
						"query": "josiah bryan",
						"fields": ["_stringified"]
					}
				}
			]
		}
	},
	"highlight": {
		"fields": { 
				"_stringified":{}
		}
	}
}

Basically, the sticker seems to be ... how do I do an OR clause where it's ($field=$value OR $field is empty)

The entire example is there in the gist ... and yes, that mapping schema is unwieldy ...I auto-generated it from my MySQL database schema ... you're welcome to critique that as well if needed, but I'm guessing my DSL is off somewhere.

Thanks!

2

Gahhh!!! False alarm. Just had to move the "should" inside the "must". Updated the gist with the working DSL, but here's the DSL that actually works:

GET /my_index/_search
{
	"_source": ["_stringified","officeid","officeid_string"],
	"query": {
		"bool": {

			"must":[
				{
					"bool": {
						"should": [
							{
								"term": {
									"officeid": "4"
								}
							},
							{
								"bool": {
									"must_not": {
										"exists": {
											"field": "officeid"
										}
									}
								}
							},
							{
							"term": {
							"system": "1"
							}
							}
						
						]
				}
				},
				{
					"multi_match": {
						"query": "josiah bryan",
						"fields": [
								"_stringified",
								"_stringified.english",
								"_stringified.trigram",
								"_stringified.autocomplete",
								"_stringified.shingle",
								"_stringified.shingle_english",
								"_stringified.trigram_english",
								"_stringified.autocomplete_english",
								"_stringified.reverse",
								"_stringified.keyword"
												
						],
						"type": "most_fields",
						"fuzziness" : "AUTO"
					}
				}
			]
		}
	},
	"highlight": {
		"fields": { 
				"_stringified":{}
		}
	}
}
1 Like