Filter result by collapsed date

Elastic Stack Elasticsearch
1

Hello,
i have a topic/news system
(a topic can have many news in different language)

i want to get the oldest news of each topic. And i want the result sorted by the oldest news displayedAt column.
ex:
topic1
-News1-t1: 2023-04-15
-News2-t1: 2023-04-18
topic2
-News1-t2: 2023-04-17
-News2-t2: 2023-04-16

Must result:
News1-t1
News2-t2

My actual code use collapse:

GET news/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "type": "news"
          }
        },
        {
          "term": {
            "onlineStatus": "online"
          }
        },
        {
          "terms": {
            "writingStatus": [
              "validated",
              "finished"
            ]
          }
        },
        {
          "exists": {
            "field": "displayedAt"
          }
        },
        {
          "bool": {
            "must_not": {
              "exists": {
                "field": "deletedAt"
              }
            }
          }
        }
      ]
    }
  },
  "collapse": {
    "field": "topic.id",
    "inner_hits": {
      "name": "oldest_news",
      "size": 1,
      "sort": [
        {
          "displayedAt": {
            "order": "asc"
          }
          
        }
      ]
    },
    "aggs": {
      "oldest_displayedAt": {
        "max": {
          "field": "oldest_news.displayedAt"
        }
      }
    }
  },
  "sort": [
    {
      "oldest_displayedAt": {
        "order": "desc"
      }
    },
    {
      
      "displayedAt": {
        "order": "desc"
      }
    }
  ], 
      
  "from": 0
}

I also tried to get my result with aggs but i always get the same problem, i can't get my final result sorted as i want.

Thank by advance for your help.

2

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