Elasticsearch multi level nested query

Question

Having a mapping with multi level nested fields like this:

{
  otherFields....,
  nestedField: {
    type: "nested",
    include_in_parent: true,
    multiple: true,
    properties: {
      province: {
        type: "nested",
        include_in_parent: true,
        multiple: true
      },
      properties: {
        comuni: {
          type: "nested",
          include_in_parent: true,
          multiple: true,
          properties: {
            nome: {
              type: "string"
            },
            parziale: {
              type: "boolean"
            }
          }
        },
        nome: {
          type: "string"
        }
      }
    }
  },
  regione: {
    type: "string"
  }
}

the documentation mentions that is possible to perform a query on this field https://www.elastic.co/guide/en/elasticsearch/reference/1.7/query-dsl-nested-query.html.

Using this query:

{
  "size": 1,
  "version": true,
  "query": {
    "filtered": {
      "query": {
        "function_score": {
          "query": {
            "bool": {
              "must": [
                {
                  "regex_term": {
                    "field_2": {
                      "term": ".*701.*",
                      "ignorecase": true
                    }
                  }
                }
              ]
            }
          },
          "functions": [
            {
              "script_score": {
                "script": "_score * -doc['field_2'].value.length()"
              }
            }
          ]
        }
      },
      "filter": {
        "nested": {
          "path": "nestedField",
          "filter": {
            "bool": {
              "must": [
                {
                  "term": {
                    "nestedField.regione": "Lazio"
                  }
                },
                {
                  "bool": {
                    "must": [
                      {
                        "or": {
                          "filters": [
                            {
                              "term": {
                                "nestedField.province.nome": "Pordenone"
                              }
                            },
                            {
                              "not": {
                                "filter": {
                                  "exists": {
                                    "field": "nestedField.province.nome"
                                  }
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "or": {
                          "filters": [
                            {
                              "term": {
                                "nestedField.province.comuni.nome": "Udine"
                              }
                            },
                            {
                              "not": {
                                "filter": {
                                  "exists": {
                                    "field": "nestedField.province.comuni.nome"
                                  }
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
}

the function score part looks good but both have some issues in the nested filter part. The data look like this:

{
  "_source": {
    "otheFields" ...,
    "nestedField": [
      {
        "regione": "Lazio"
      },
      {
        "province": [
          {
            "nome": "Venezia"
          },
          {
            "nome": "Treviso"
          }
        ],
        "regione": "Veneto"
      },
      {
        "province": [
          {
            "comuni": [
              {
                "nome": "Varmo",
                "parziale": false
              }
            ],
            "nome": "Udine"
          }
        ],
        "regione": "Friuli venezia giulia"
      }
    ]
  }
}

The query doesn't find the given record even if the province field is missing, instead it work fine if we use "Veneto" for regione and "Treviso" for provincia.nome and also using the comune field in the other nested object.

Why this query doesn't work ?

Sloan Ahrens Sloan Ahrens · Accepted Answer · 2015-10-09T18:28:38

Trying changing your terms to lower-case. Since you don't specify an analyzer in your mapping, the standard analyzer is used, which will convert terms to lower-case.

Your query is pretty involved, and at first I thought you might need more "nested" clauses, but when I did the following, it seemed to work. (I did go through this pretty quickly, so let me know if what I've shown here doesn't work for you for some reason.)

I had to take out the "query" part since I got an error on "regex_term", but if I'm reading it right that shouldn't affect the outcome if we only have the one document.

So I created an index like this (one of your braces was in the wrong place, after the definition of "province"):

PUT /test_index
{
   "settings": {
      "number_of_shards": 1
   },
   "mappings": {
      "doc": {
         "properties": {
            "nestedField": {
               "type": "nested",
               "include_in_parent": true,
               "multiple": true,
               "properties": {
                  "province": {
                     "type": "nested",
                     "include_in_parent": true,
                     "multiple": true,
                     "properties": {
                        "comuni": {
                           "type": "nested",
                           "include_in_parent": true,
                           "multiple": true,
                           "properties": {
                              "nome": {
                                 "type": "string"
                              },
                              "parziale": {
                                 "type": "boolean"
                              }
                           }
                        },
                        "nome": {
                           "type": "string"
                        }
                     }
                  }
               },
               "regione": {
                  "type": "string"
               }
            }
         }
      }
   }
}

added your doc:

PUT /test_index/doc/1
{
   "nestedField": [
      {
         "regione": "Lazio"
      },
      {
         "province": [
            {
               "nome": "Venezia"
            },
            {
               "nome": "Treviso"
            }
         ],
         "regione": "Veneto"
      },
      {
         "province": [
            {
               "comuni": [
                  {
                     "nome": "Varmo",
                     "parziale": false
                  }
               ],
               "nome": "Udine"
            }
         ],
         "regione": "Friuli venezia giulia"
      }
   ]
}

Then ran this modified query:

POST /test_index/_search
{
   "size": 1,
   "version": true,
   "query": {
      "filtered": {
         "filter": {
            "nested": {
               "path": "nestedField",
               "filter": {
                  "bool": {
                     "must": [
                        {
                           "term": {
                              "nestedField.regione": "lazio"
                           }
                        },
                        {
                           "bool": {
                              "must": [
                                 {
                                    "or": {
                                       "filters": [
                                          {
                                             "term": {
                                                "nestedField.province.nome": "pordenone"
                                             }
                                          },
                                          {
                                             "not": {
                                                "filter": {
                                                   "exists": {
                                                      "field": "nestedField.province.nome"
                                                   }
                                                }
                                             }
                                          }
                                       ]
                                    }
                                 },
                                 {
                                    "or": {
                                       "filters": [
                                          {
                                             "term": {
                                                "nestedField.province.comuni.nome": "udine"
                                             }
                                          },
                                          {
                                             "not": {
                                                "filter": {
                                                   "exists": {
                                                      "field": "nestedField.province.comuni.nome"
                                                   }
                                                }
                                             }
                                          }
                                       ]
                                    }
                                 }
                              ]
                           }
                        }
                     ]
                  }
               }
            }
         }
      }
   }
}

and the document was returned:

{
   "took": 4,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 1,
      "max_score": 1,
      "hits": [
         {
            "_index": "test_index",
            "_type": "doc",
            "_id": "1",
            "_version": 1,
            "_score": 1,
            "_source": {
               "nestedField": [
                  {
                     "regione": "Lazio"
                  },
                  {
                     "province": [
                        {
                           "nome": "Venezia"
                        },
                        {
                           "nome": "Treviso"
                        }
                     ],
                     "regione": "Veneto"
                  },
                  {
                     "province": [
                        {
                           "comuni": [
                              {
                                 "nome": "Varmo",
                                 "parziale": false
                              }
                           ],
                           "nome": "Udine"
                        }
                     ],
                     "regione": "Friuli venezia giulia"
                  }
               ]
            }
         }
      ]
   }
}

Here's all the code in one place:

http://sense.qbox.io/gist/3b187e0fe22651a42501619ff867e02501b81f9e

Elasticsearch multi level nested query

1 Answers