Multi filter on nested object

Elastic Stack Elasticsearch
1

Is there a way to do a multi filter on a nested object to do something
similar to left joining a table on itself in sql?

I have a nested object list which contains multiple object types. They all
share the same id field as they are all of the same super Class type.
ES creates a nested object using the super Class type.

I am trying to create a filter which will filter based on the object type
and the id, I also need to filter more then one object type at the same
time.
I cannot use an OR filter because I need an exact match. When using an AND
filter to join the nested filters it seems to just treat them as the same
filter
and returns nothing.

Here is an example of my query:

{
"query": {
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"and": {
"filters": [
{
"and": {
"filters": [
{
"nested": {
"filter": {
"and": {
"filters": [
{
"and": {
"filters": [
{
"term": {
"nested.data.type": "1"
}
},
{
"range": {
"nested.data.id": {
"from": 11,
"to": 15,
"include_lower": true,
"include_upper": true
}
}
}
]
}
},
{
"and": {
"filters": [
{
"term": {
"nested.data.type": "3"
}
},
{
"term": {
"nested.data.id": 25
}
}
]
}
}
]
}
},
"path": "nested"
}
}
]
}
}
]
}
}
}
}
}

--

2

I found a solution by using a nested filter for each object type in the
list and ANDing them together. The overall filter which includes the nested
filters returns the expected result. So it seems that the term and range
filters cannot be included in a single nested filter to get the same result.

--