Elasticsearch with nested objects query

Question

I have an index with a nested mapping. I want to preform a query that will return the following: give me all the documents where each word in the search term appears in one or more of the nested documents.

Here is the index:

properties: {
      column_values_index_as_objects: {
        type: "nested",
        properties: {
          value: {
            ignore_above: 256,
            type: 'keyword',
            fields: {
              word_middle: {
                analyzer: "searchkick_word_middle_index",
                type: "text"
              },
              analyzed: {
                term_vector: "with_positions_offsets",
                type: "text"
              }
            }
          }
        }
      }
    }

Here is the latest query I try:

nested: {
       path: "column_values_index_as_objects",
       query: {
          bool: {
             must: [
                {
                   match: {
                       "column_values_index_as_objects.value.analyzed": {
                       query: search_term,
                       boost: 10 * boost_factor,
                       operator: "or",
                       analyzer: "searchkick_search"
                      }
                   }
                }

For example if I search the words 'food and water', I want that each word will appear in at least on nested document. The current search returns the document even if only one of the words exists

Thanks for the help!

Update: As Cristoph suggested, the solution works. now I have the following problem.

Here is my index:

  properties: {
      name: {
        type: "text"
      },
      column_values_index_as_objects: {
        type: "nested",
        properties: {
          value: {
            ignore_above: 256,
            type: 'keyword',
            fields: {
              word_middle: {
                analyzer: "searchkick_word_middle_index",
                type: "text"
              },
              analyzed: {
                term_vector: "with_positions_offsets",
                type: "text"
              }
            }
          }
        }
      }
    }

And the query I want to preform is if I search for 'my name is guy', and will give all the documents where all the words are found - might be in the nested documents and might in the name field. For example, I could have a document with the value 'guy' in the name field and other words in the nested documents

If you want AND in terms then operator value should be and instead of or. — Nishant
Thanks! if I use 'AND' it means it will give me results only if the WHOLE sentence is in the value. And this is not what I want. I want that each word will appear inn at least one sub document — Guy Asinovsky

Christophe Quintard Christophe Quintard · Accepted Answer · 2018-12-24T09:28:10

In order to do this, I usually split the terms and generate a request like this (foo:bar is an other criteria on an other field) :

{
  "bool": {
    "must": [
      {
        "nested": {
          "path": "column_values_index_as_objects",
          "query": {
            "match": {
              "column_values_index_as_objects.value.analyzed": {
                "query": "food",
                "boost": "10 * boost_factor",
                "analyzer": "searchkick_search"
              }
            }
          }
        }
      },
      {
        "nested": {
          "path": "column_values_index_as_objects",
          "query": {
            "match": {
              "column_values_index_as_objects.value.analyzed": {
                "query": "and",
                "boost": "10 * boost_factor",
                "analyzer": "searchkick_search"
              }
            }
          }
        }
      },
      {
        "nested": {
          "path": "column_values_index_as_objects",
          "query": {
            "match": {
              "column_values_index_as_objects.value.analyzed": {
                "query": "water",
                "boost": "10 * boost_factor",
                "analyzer": "searchkick_search"
              }
            }
          }
        }
      },
      {
        "query": {
          "term": {
            "foo": "bar"
          }
        }
      }
    ]
  }
}

Elasticsearch with nested objects query

1 Answers