I need to filter items by a certain field value, only if that field exists, otherwise it should include items which don't have that field value
Here's the example, a Todo app with assigned users and projects. I need to be able to get all items which are assigned to userA or userB and if any or the items are part of a project, only get the items from `projectX.
I'm using "must" to get items which are assigned to the users, this works as expected. But when I try to use "should" and "must" to get items where if projectId.keyword exists make sure that it equals projectX, but I also get items from other projects, i.e. projectY and projectZ, so the projectId.keyword constraint isn't working.
Here's my query.
{
"query": {
"bool": {
"must": {
"terms": {
"assignedTo.keyword": [
"userA",
"userB"
]
}
},
"should": [
{
"bool": {
"must": [
{
"exists": {
"field": "projectId.keyword"
}
},
{
"term": {
"projectId.keyword": "projectX"
}
}
]
}
},
{
"bool": {
"must_not": [
{
"exists": {
"field": "projectId.keyword"
}
}
]
}
}
]
}
}
}
Maybe there's a way to use must_not to make sure projectId.keyword doesn't equal something other than projectX?
