Query and sort inside nested object

[Are]

Edit: see step-by-step and deeper explanation in post below. I am trying to sort my search results based on a field inside a nested object. Running ES 7.9. The mapping of the nested object looks like this:

{
  "metaDates": {
    "type": "nested",
    "properties": {
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "qualifiers": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "value": {
        "type": "date",
        "format": "date_optional_time"
      }
    }
  }
}

So there can be many metaDates objects inside one document. Those nested objects will typically look like this:

{
  "metaDates": [
    {
      "name": "create-time",
      "value": "2020-06-03T13:14:11.556Z",
      "qualifiers": [
        "*"
      ]
    },
    {
      "name": "update-time",
      "value": "2020-06-03T13:14:11.556Z",
      "qualifiers": [
        "*"
      ]
    }
  ]
}

What I want to achieve is to order the results based on either create-time or update-time. Ascending and/or descending.

I can't make this to fully work. As it currently stands I can't enforce it to only look at the given date, it seems to me as it validates all metaDates values instead of just the one I'm interested in when ordering. (setting sort mode affects the results, that's why I think that is the case)

Sample full document:

{
  "_index": "index-someitem-1630649750000",
  "_type": "_doc",
  "_id": "17",
  "_score": null,
  "_source": {
    "id": 17,
    "title": "test",
    "metaTexts": [
      {
        "name": "title",
        "value": "test",
        "qualifiers": [
          "en_US"
        ]
      }
    ],
    "metaDates": [
      {
        "name": "create-time",
        "value": "2020-06-03T13:14:24.502Z",
        "qualifiers": [
          "*"
        ]
      },
      {
        "name": "update-time",
        "value": "2020-06-03T13:14:29.140Z",
        "qualifiers": [
          "*"
        ]
      }
    ]
  },
  "sort": [
    1591190064502
  ]
}

I want the whole document to be sorted based on the either create-time or update-time.

Any help is much appreciated! Thank you

[spinscale (Alexander Reelsen)]

Hey,

it would be great if you could a create fully reproducible example, that contains maybe more than one document, so you can show with a request how the sorting is off compared how you expect to be instead.

Also, if you provide your samples with the full recreation mode (i.e. how it is done in kibana including HTTP endpoint and verb, so people can copy & paste this in their own kibana instance) will increase your chances of getting help a lot.

Thank you!

--Alex

[Are]

Sure thing Alexander. See below for step-by-step on how to recreate the issue I am having:
1. Create index

PUT nested-object-test
{
  "mappings": {
    "properties": {
      "metaDates": {
          "type": "nested",
          "properties": {
            "value": {
              "type": "date"
            }
          }
      }
    }
  }
}

2. Insert 3 documents with various dates in metaDates

PUT /nested-object-test/_doc/1
{
  "somefield": "this is a field",
  "someotherfield": "this is some other field",
  "metaDates": [
    {
      "name": "create-time",
      "value": "2021-06-05T08:17:23.502Z"
    },
    {
      "name": "update-time",
      "value": "2020-06-05T08:17:23.502Z"
    },
    {
      "name": "some-other-time",
      "value": "2020-01-01T08:17:23.502Z"
    }
  ]
}

PUT /nested-object-test/_doc/2
{
  "somefield": "this is a field 2",
  "someotherfield": "this is some other field 2",
  "metaDates": [
    {
      "name": "create-time",
      "value": "2019-06-05T08:17:23.502Z"
    },
    {
      "name": "update-time",
      "value": "2021-06-05T08:17:23.502Z"
    },
    {
      "name": "some-other-time",
      "value": "2019-01-01T08:17:23.502Z"
    }
  ]
}

PUT /nested-object-test/_doc/3
{
  "somefield": "this is a field 3",
  "someotherfield": "this is some other field 3",
  "metaDates": [
    {
      "name": "create-time",
      "value": "2018-06-05T08:17:23.502Z"
    },
    {
      "name": "update-time",
      "value": "2021-01-01T10:17:23.502Z"
    },
    {
      "name": "some-other-time",
      "value": "2017-01-01T08:17:23.502Z"
    }
  ]
}

3. Do a sample query, expecting document results to be sorted based on filter provided in search (in this example by update-time)

GET nested-object-test*/_search
{
  "sort": [
    {
      "metaDates.value": {
        "order": "desc",
        "nested": {
          "path": "metaDates",
          "filter": {
            "match":{
              "metaDates.name": "update-time"
            }
          }
        }
      }
    }
  ]
}

4. Observe that documents returned are not sorted according to update-time

So I want the documents to be sorted according to update-time and as it is now it seems to me that it does not specifically look at that date, but rather all dates (metaDates.value) in metaDates.

Hope this helps understanding the issue (and that it is solvable ) Thank you