Unsure how to structure ElasticSearch Range Query with nested properties

Question

My object in ES looks like:

{
  "_index": "myIndex",
  "_type": "myType",
  "_id": "75fd98d2-eca7-4a94-9dd8-1cc2c9b1fbbf",
  "_version": 2,
  "found": true,
  "_source": {
    "account_id": "100",
    "import_ids": [
      "4f4eef42-5493-464e-ac08-68a3a25a01fb"
    ],
    "accept": "html",
    "deleted_at": null,
    "signup_form_ids": [
      {
        "timestamp": "2015-11-23T20:08:11.604000",
        "signup_form_id": "1234"
      }
    ],
    "mailing_status": "active",
    "group_ids": [
      "0eddd2c0-ce70-4eb7-bcd8-9e41e41ac0b3"
    ],
    "confirmed_opt_in_at": null,
    "fields": [
      {
        "text_value": "My Company",
        "name": "company"
      },
      {
        "text_value": "Foo",
        "name": "first-name"
      },
      {
        "text_value": "Bar",
        "name": "last_name"
      },
      {
        "text_value": "555-555-5555",
        "name": "phone"
      }
    ],
    "created_at": "2015-11-23T19:20:15.889000",
    "last_modified_at": "2015-11-23T20:08:11.604000",
    "bounce_count": 0,
    "opted_out_at": null,
    "archived_at": null,
    "email": "[email protected]",
    "opt_out_mailing_id": "None"
  }
}

I am trying to run write a query that gives me all hits where the signup_form_ids.timestamp are lte now-7d/d. I'm looking at https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#ranges-on-dates but unsure how to structure the query

This is what I have so far:

{
    "query": {
        "nested": {
            "path": "signup_form_ids",
            "bool": {
                "must": [
                    {
                        "range": {
                            "timestamp" {
                                "lte": "now-7d/d"
                            }
                        }
                    }
                ]
            }
        },
        "bool": {
            "must": [
                {
                    "bool": {
                        "must": []
                    }
                },
                {
                    "match": {
                        "account_id": "100"
                    }
                },
                {
                    "filtered": {
                        "filter": {
                            "missing": {
                                "field": "deleted_at"
                            }
                        }
                    }
                }
            ]
        }
    },
    "size": 500,
    "from": 0
}

Sloan Ahrens Sloan Ahrens · Accepted Answer · 2015-11-23T22:47:02

There are several things wrong here, and it's not entirely obvious which ones are artifacts of you adjusting your query to post here.

First, you're missing a colon after "timestamp" in your query. Also, you have an empty inner "bool". And your "range" query is inside a needless "bool". Also your "filtered" clause is redundant and you can just use the "filter" inside it.

But the main problems are that 1) your "nested" query needs to be inside your "bool" if you want all the conditions to apply, 2) your "nested" "range" filter needs to specify the full path to "timestamp" and 3) the "bool" inside your "nested" clause needs to be in a "filter".

So, making minimal adjustments to make the query work, the following query returns the document you posted (I changed the "lte" to "gte" so the document you posted would be returned, otherwise it doesn't match the query, yet):

POST /test_index/_search
{
   "query": {
      "bool": {
         "must": [
            {
               "bool": {
                  "must": []
               }
            },
            {
               "match": {
                  "account_id": "100"
               }
            },
            {
               "filtered": {
                  "filter": {
                     "missing": {
                        "field": "deleted_at"
                     }
                  }
               }
            },
            {
               "nested": {
                  "path": "signup_form_ids",
                  "filter": {
                     "bool": {
                        "must": [
                           {
                              "range": {
                                 "signup_form_ids.timestamp": {
                                    "gte": "now-7d/d"
                                 }
                              }
                           }
                        ]
                     }
                  }
               }
            }
         ]
      }
   },
   "size": 500,
   "from": 0
}

If I clean it up to remove all the redundancies, I end up with:

POST /test_index/_search
{
   "query": {
      "bool": {
         "must": [
            {
               "match": {
                  "account_id": "100"
               }
            },
            {
               "missing": {
                  "field": "deleted_at"
               }
            },
            {
               "nested": {
                  "path": "signup_form_ids",
                  "filter": {
                     "range": {
                        "signup_form_ids.timestamp": {
                           "gte": "now-7d/d"
                        }
                     }
                  }
               }
            }
         ]
      }
   },
   "size": 500,
   "from": 0
}

Here is some code I used to play around with it:

http://sense.qbox.io/gist/ee96042c0505dfb07199b919d134b2a20c5a66fd

Unsure how to structure ElasticSearch Range Query with nested properties

1 Answers