CODEWITHSUNDEEP
elasticsearch
Marzouk
Marzouk

Reputation: 2713

In Elasticsearch, how to sort the retrieved result by nested field's property

In Elasticsearch, let's say that I have created a users index. This index has inner field as nested so I can treat it as array.

Example of the inserted data:

[{
        “isActive”: true,
        “id”: “0183284A-92BE-4CD7-8C76-98225562B4CB”,
        “actions”: [{
                “createdDate”: 1623895434,
                “userId”: “2B042D11-FD7E-4919-832A-ADBA3C63160C”,
                “type”: 0
            },
            {
                “createdDate”: 1623896475,
                “userId”: “4E3B2ACF-8D21-4CA8-865A-5A128873A6D9”,
                “type”: 0
            }
        ]
    },
    {
        “isActive”: true,
        “id”: “2B042D11-FD7E-4919-832A-ADBA3C63160C”,
        “actions”: [{
                “createdDate”: 1623805703,
                “userId”: “0183284A-92BE-4CD7-8C76-98225562B4CB”,
                “type”: 0
            },
            {
                “createdDate”: 1623895929,
                “userId”: “4E3B2ACF-8D21-4CA8-865A-5A128873A6D9”,
                “type”: 0
            }
        ]
    },
    {
        “isActive”: true,
        “id”: “4E3B2ACF-8D21-4CA8-865A-5A128873A6D9”,
        “actions”: [{
            “createdDate”: 1623893475,
            “userId”: “0183284A-92BE-4CD7-8C76-98225562B4CB”,
            “type”: 0
        }]
    }
]

So now say that I inserted the above 3 objects to the users index and mapped the actions field as nested.

I need to be able to execute a query to retrieve all users that who took action against another user:

GET users/_search?pretty
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "actions",
            "query": {
              "bool": {
                "must": [
                  { "match": { "actions.userId.keyword": "4E3B2ACF-8D21-4CA8-865A-5A128873A6D9" }
                  }
                ]
              }
            },
            "inner_hits": { }
          }
        }
      ]
    }   
  }, 
  "_source": false
}

So this will return all users that have actions included userId = 4E3B2ACF-8D21-4CA8-865A-5A128873A6D9:

{
    "took": 1,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 2,
            "relation": "eq"
        },
        "max_score": 0.6931471,
        "hits": [{
                "_index": "users",
                "_type": "_doc",
                "_id": "0183284A-92BE-4CD7-8C76-98225562B4CB",
                "_score": 0.6931471,
                "inner_hits": {
                    "actions": {
                        "hits": {
                            "total": {
                                "value": 1,
                                "relation": "eq"
                            },
                            "max_score": 0.6931471,
                            "hits": [{
                                "_index": "users",
                                "_type": "_doc",
                                "_id": "0183284A-92BE-4CD7-8C76-98225562B4CB",
                                "_nested": {
                                    "field": "actions",
                                    "offset": 0
                                },
                                "_score": 0.6931471,
                                "_source": {
                                    "createdDate": 1623896475,
                                    "userId": "4E3B2ACF-8D21-4CA8-865A-5A128873A6D9",
                                    "type": 0
                                }
                            }]
                        }
                    }
                }
            },
            {
                "_index": "users",
                "_type": "_doc",
                "_id": "2B042D11-FD7E-4919-832A-ADBA3C63160C",
                "_score": 0.6931471,
                "inner_hits": {
                    "actions": {
                        "hits": {
                            "total": {
                                "value": 1,
                                "relation": "eq"
                            },
                            "max_score": 0.6931471,
                            "hits": [{
                                "_index": "users",
                                "_type": "_doc",
                                "_id": "2B042D11-FD7E-4919-832A-ADBA3C63160C",
                                "_nested": {
                                    "field": "actions",
                                    "offset": 0
                                },
                                "_score": 0.6931471,
                                "_source": {
                                    "createdDate": 1623895929,
                                    "userId": "4E3B2ACF-8D21-4CA8-865A-5A128873A6D9",
                                    "type": 0
                                }
                            }]
                        }
                    }
                }
            }
        ]
    }
}

Now I want to sort all the retrieved users based on actions.createdDate. How to do similar thing please?

Upvotes: 1

Views: 46

Answers (1)

Nate
Nate

Reputation: 10671

Would a script_score query work for you here? We'd continue to require the query matching you have now by userID but then score results based on the createdDate value with the script portion.

GET users/_search?pretty
{
  "query": {
    "nested": {
      "path": "actions",
      "query": {
        "script_score": {
          "query": {
            "match": { "actions.userId.keyword": "4E3B2ACF-8D21-4CA8-865A-5A128873A6D9" }
          },
          "script": {
            "source": "doc['actions.createdDate'].value"
          }
        }
      }
    }
  },
  "_source": false
}

If that runs it would sort the results descending by createdDate, you could use the reciprocal to sort ascending.

Upvotes: 1

Related Questions