How do I create an ElasticSearch filter on two nested objects so that the results only includes documents that match both conditions

[fitterweb (Jim Keeney)]

How do I create an ElasticSearch filter query on nested objects so that the results match both?

I have documents that include nested objects like the following:

{
"objectNumber": "1",
"nestedObjects": [
    {
        "category": "position",
        "tag": "2300"
    },        
    {
        "category": "scope",
        "tag": "External"
    }  

    ]

}

{

"objectNumber": "2",
"nestedObjects": [
{
"category": "position",
"tag": "2300"
},
{
"category": "scope",
"tag": "Internal"
}

]

}

{

"objectNumber": "3",
"nestedObjects": [
{
"category": "position",
"tag": "2300"
},
{
"category": "scope",
"tag": "Internal"
}

]

}

And I would like to execute a query that returns only the documents that have a nested object with category=position and tag=2300 AND a nested object with category=scope and tag=Internal . objectNumber 2 and 3 but not 1.

I've gotten as far as this:

{
  "query": {
    "nested": {
      "path": "tags",
      "query": {
        "bool": {
          "should": [
            {
              "bool": {
                "filter": [
                  {
                    "match": {
                      "tags.category": "position"
                    }
                  },
                  {
                    "match": {
                      "tags.tag": "2200"
                    }
                  },
                  {
                    "match": {
                      "tags.category": "scope"
                    }
                  },
                  {
                    "match": {
                      "tags.tag": "Internal"
                    }
                  }
                ]
              }
            }

          ]
        }
      }
    }
  }
}

Which returns all 3 objects and not just 2 and 3. Any help would be greatly appreciated.

[spinscale (Alexander Reelsen)]

Hey,

please take your time to properly format your JSON, as it makes things much easier to read, especially in such a case where formatting is so crucial due to be able to understand the query

how about a single bool query, with two filter clauses. Each of those clauses is a nested query, which in turn is a bool query with two filter clauses for category and the tag field.

hope that makes a bit sense when reading

--Alex

[fitterweb (Jim Keeney)]

Alexander -

Thank you for the help. Here is the JSON for the example objects that I posted properly formatted:

{
    "objectNumber": "1",
    "nestedObjects": [
        {
            "category": "position",
            "tag": "2300"
        },        
        {
            "category": "scope",
            "tag": "External"
        }  

        ]
}

{

"objectNumber": "2",
"nestedObjects": [
    {
        "category": "position",
        "tag": "2300"
    },        
    {
        "category": "scope",
        "tag": "Internal"
    }  

    ]
}

{

"objectNumber": "3",
"nestedObjects": [
    {
        "category": "position",
        "tag": "2300"
    },        
    {
        "category": "scope",
        "tag": "Internal"
    }  

    ]
}

I have been working on following your guidance but keep struggling with how to pair two filter queries with each other. I keep getting different forms of :

"[filtered] query malformed, no start_object after query name"

Is there documentation on how things should be nested and how to properly setup arrays of queries?

Here is as far as I've gotten. I think I'm just missing brackets or something.

{
  "query": {
    "filtered": [ 
      {
    "bool":  {
      "filter": {
        "nested": {
          "path": "tags",
          "query": [
            {
              "match": {
                "tags.category": "position"
              }
            },
            {
              "match": {
                "tags.tag": "2200"
              }
            }
          ]
        }
      }
    }
    
  },
  {
    "bool":  {
      "filter": {
        "nested": {
          "path": "tags",
          "query": [
            {
              "match": {
                "tags.category": "scope"
              }
            },
            {
              "match": {
                "tags.tag": "Internal"
              }
            }
          ]
        }
      }
    }
    
  }
  ]}
}

Your continued help would be greatly appreciated.

Thanks.

Jim K.

[spinscale (Alexander Reelsen)]

it's a bit tricky to get the building blocks idea of a bool query right for the first time.

remove the filtered part, have a single bool, sth like this (on top of my head not valid JSON of course)

bool: {
  filter: [
    {
      "nested" :  {
        "path" : "...",
        "query" : {
           "bool" : { filter: [ { tags.category=position }, { tags.tag=2200 } ] }
         }
      }
     },
    {
      "nested" :  {
        "path" : "...",
        "query" : {
           "bool" : { filter: [ { category=scope }, { ... } ] }
         }
      }
     }
  ]
}

hope this makes sense

[fitterweb (Jim Keeney)]

Got it. This works:

{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "tags",
            "query": [
              {
                "match": {
                  "tags.category": "position"
                }
              },
              {
                "match": {
                  "tags.tag": "2200"
                }
              }
            ]
          }
        },
        {
          "nested": {
            "path": "tags",
            "query": [
              {
                "match": {
                  "tags.category": "scope"
                }
              },
              {
                "match": {
                  "tags.tag": "Internal"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

My sense is there are other ways to structure this as well. I'll keep playing until I understand all the ins and outs. Is there a guide to what can be an array and what cannot?

[spinscale (Alexander Reelsen)]

hey,

in a bool query, every leaf can be an array, as it can contain an arbitrary number of sub queries including nesting more bool queries. Most other queries cannot have an array for their clauses on top of my head.

--Alex