Can i sort my documents by date several conditions?

Elastic Stack Elasticsearch
1

I have index with document which have two field with dates "match_date_time" and "created_at".
I need query for getting documents sorted by next criteria

1. [ASC order] articles with future match date and time (sorted by “"match_date_time”)
2. [DESC order] articles with expired match date and time (sorted by “match_date_time”)
3. [DESC order] articles without match date and time (sorted by “created_at”)

For example we have 6 articles

[
    {
      "id": 1,
      "match_date_time": "2023-01-28T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
    {
      "id": 2,
      "match_date_time": "2023-01-27T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
    {
      "id": 3,
      "match_date_time": "2023-01-23T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
    {
      "id": 4,
      "match_date_time": "2023-01-24T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
    {
      "id": 5,
      "match_date_time": null,
      "created_at": "2021-11-08T13:12:00+00:00"
    },

    {
      "id": 6,
      "match_date_time": null,
      "created_at": "2021-11-24T13:12:00+00:00"
    }
  ]

Current date is 2023-01-26 From elastic we should get next sequence

[
    //future date, sort asc  by match_date_time
    {
      "id": 2,
      "match_date_time": "2023-01-27T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
   //future date, sort asc  by match_date_time
    {
      "id": 1,
      "match_date_time": "2023-01-28T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
     //expired date, sort desc  by match_date_time
    {
      "id": 4,
      "match_date_time": "2023-01-24T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
    //expired date, sort desc  by match_date_time
    {
      "id": 3,
      "match_date_time": "2023-01-23T13:12:00+00:00",
      "created_at": "2021-11-08T13:12:00+00:00"
    },
    //match_date_time not exists sort desc  by created_at 
    {
      "id": 6,
      "match_date_time": null,
      "created_at": "2021-11-24T13:12:00+00:00"
    },
     //match_date_time not exists sort desc  by created_at 
    {
      "id": 5,
      "match_date_time": null,
      "created_at": "2021-11-08T13:12:00+00:00"
    }
  ]

It is possible to get data by one query?

2

Hi Volodymyr,

You can specify multiple fields for sorting with appropriate directions for each. The first example in the docs should help show the syntax.

3

but my sort criteria depend on field value. Documents which have match_date_time in future should be first and sorted by field match_date_time [ASC order], after them should be documents which have match_date_time in past and sorted by match_date_time [DESC order].

4

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