Filter and sort based on attributes in Terms lookup document

Elastic Stack Elasticsearch
1

I have documents which contain a users collection which are linked to other documents through the documents id attribute like so:

PUT /collection/1
{
    "items": [
        {"id": 1, "time_added": "2017-08-07T09:07:15.000Z", "condition": "fair"},
        {"id": 3, "time_added": "2019-08-07T09:07:15.000Z", "condition": "good"},
        {"id": 4, "time_added": "2016-08-07T09:07:15.000Z", "condition": "poor"}
    ]
}

I then use a terms query to get a users collection like so:

GET /documents/_search
{
    "query" : {
        "terms" : {
            "_id" : {
                "index" : "index",
                "type" : "collection",
                "id" : 1,
                "path" : "items.id"
            }
        }
    }
}

This works fine. I get the documents and can search, sort and use aggregations like I want.

But is there a way to aggregate, filter and sort those documents based on the time_added or condition attributes in the collection document? Say I wanted to sort based on time_added or filter for condition=="good"?

2

Have still not found a solution but I found this thread that discusses the sorting, but I am unable to get it to work.

Following the directions from there here's some more detail on what I'm trying to do.

  1. I put a nested mapping for the collection type:
PUT /index/collection/_mappings
{
     "properties": {
         "items": {
             "type": "nested" 
         }
     }
}
  1. I index some documents/things:
POST /index/thing/_bulk
    { "index":{ "_id": 1 } }
    { "title":"One thing"}
    { "index":{ "_id": 2 } }
    { "title":"Second thing"}
    { "index":{ "_id": 3 } }
    { "title":"Three things"}
    { "index":{ "_id": 4 } }
    { "title":"And so fourth"}
    { "index":{ "_id": 5 } }
    { "title":"Five things"}
  1. I index the collection, which has three items in the following order:
PUT /index/collection/1
{
    "items": [
        {"id": 1, "time_added": "2017-08-07T09:07:15.000Z", "condition": "fair"},
        {"id": 3, "time_added": "2019-08-07T09:07:15.000Z", "condition": "good"},
        {"id": 4, "time_added": "2016-08-07T09:07:15.000Z", "condition": "poor"}
    ]
}
  1. I run a search with a terms lookup, filtering the documents that are in the collection 
GET "index/thing/_search"
{
    "query" : {
        "terms" : {
            "_id" : {
                "index" : "index",
                "type" : "collection",
                "id" : 1,
                "path" : "items.id"
            }
        }
    },        
    "sort": [
        { "items.id" : {"order" : "desc"}}
    ]
}

The order I expect is 1,3,4 (which is the order in the collection) but i get them in 4,1,3 (and in fact changing the order from desc to asc does not change the order). Am I missing something?

c.c. @polyfractal as you had an answer on the linked thread (I hope tagging like this is acceptable, quite a n00b to this forum).

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.