Elastic Search Difficult Query with Grouping

I am new to Elastic Search (1.7). I have the ES index of a client’s versions.

Each client has a unique Id (client_id).

There are many versions of a client can be in the index.

Each version of a client has a unique id (version_id) for the client.

Index Data Example:

{
  “client_id”: "1",
  "version_id": "1",
  “versionCreationDate”: "2015-11-06T10:02:22.597Z",
  “clientName”: “FirstName LastName”
}
…,
{
  “client_id”: "1",
  "version_id": "n",
  “versionCreationDate”: …
  “clientName”: “FirstName LastName”
},
{
  “client_id”: "2",
  "version_id": "1",
  “versionCreationDate”: …
  “clientName”: “FirstName LastName”
},
…
{
  “client_id”: "2",
  "version_id": "n",
  “versionCreationDate”: …
  “clientName”: “FirstName LastName”
},
…
{
  “client_id”: "N",
  "version_id": "1",
  “versionCreationDate”: …
  “clientName”: “FirstName LastName”
},
…
{
  “client_id”: "N",
  "version_id": "n",
  “versionCreationDate”: …
    “clientName”: “FirstName LastName”
}

I need to find client’s versions by input parameters for query:

<clientName>, <VersionCreationDateMax>.

Client's versions should match <clientName> (with fuzziness=auto). And any version must have maximum value of versionCreationDate for this client, but <= <VersionCreationDateMax>. So the only one version of each client should be in search results (newest, but not more than <VersionCreationDateMax>)

Please help me in finding the filter/query to do that

Answers


You can achieve this with the following query:

  • one range filter to specify the max versionCreationDate
  • then one terms aggregation on the client_id field
  • finally, for each client bucket, one top_hits sub-aggregation sorted in desc order on the versionCreationDate field and only returning one document (i.e. size: 1)

Here is the query:

{
  "query": {
    "filtered": {
      "filter": {
        "range": {
          "versionCreationDate": {
            "lt": "2015-12-02T00:00:00.000Z"
          }
        }
      }
    }
  },
  "aggs": {
    "clients": {
      "terms": {
        "field": "client_id"
      },
      "aggs": {
        "latest": {
          "top_hits": {
            "sort": [
              {
                "versionCreationDate": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}

Need Your Help

SQLite, SQL: Using UPDATE or INSERT accordingly

sql sqlite sql-update sql-insert

Basically, I want to insert if a given entry (by its primary key id) doesn't exist, and otherwise update if it does. What might be the best way to do this?

IndexOutOfBoundsException when adding items to arraylist from sharedpreferences

android exception arraylist sharedpreferences indexoutofboundsexception

I got a little issue with my arraylist and I need a workaround to avoid the IndexOutOfBoundsException exception.