in elasticsearch,how to order by count

I have two tables: users and articles, now i want to get a user list that order by everyone's articles with date's range.

I set the elasticsearch index :

PUT /users/_mapping
{
  "properties":{
    "id": {
      "type": "long"
    },
    "create_time": {
      "type": "date",
      "format": "yyyy-MM-dd"
    },
    "articles":{
      "type":"nested",
      "properties": {
        "id": {
          "type": "long"
        },
        "content": {
          "type": "text"
        },
        "create_time": {
          "type": "date",
          "format": "yyyy-MM-dd"
        }
      }
    }
  }
}

the data like this:

{
  "id":1,
  "create_time": "2023-12-20",
  "articles": [
    {"id": 21, "content":"1111", "create_time": "2023-12-20"},
    {"id": 22, "content":"1111", "create_time": "2023-12-21"},
    {"id": 23, "content":"1111", "create_time": "2023-12-21"},
    {"id": 24, "content":"1111", "create_time": "2023-12-22"},
    {"id": 24, "content":"1111", "create_time": "2023-12-23"}
  ]
}

{
  "id":2,
  "create_time": "2023-12-20",
  "articles": [
    {"id": 21, "content":"1111", "create_time": "2023-12-20"},
    {"id": 22, "content":"1111", "create_time": "2023-12-21"},
    {"id": 23, "content":"1111", "create_time": "2023-12-22"},
    {"id": 24, "content":"1111", "create_time": "2023-12-22"},
    {"id": 24, "content":"1111", "create_time": "2023-12-22"},
    {"id": 25, "content":"1111", "create_time": "2024-12-31"}
  ]
}

{
  "id":3,
  "create_time": "2023-12-20",
  "articles": [
    {"id": 21, "content":"1111", "create_time": "2023-12-20"},
    {"id": 22, "content":"1111", "create_time": "2023-12-21"},
    {"id": 23, "content":"1111", "create_time": "2023-12-21"},
    {"id": 24, "content":"1111", "create_time": "2023-12-21"},
    {"id": 24, "content":"1111", "create_time": "2023-12-22"},
    {"id": 25, "content":"1111", "create_time": "2024-12-31"}
  ]
}

{
  "id":4,
  "create_time": "2023-12-10",
  "articles": [
    {"id": 21, "content":"1111", "create_time": "2023-12-10"},
    {"id": 22, "content":"1111", "create_time": "2023-12-11"},
    {"id": 23, "content":"1111", "create_time": "2023-12-21"},
    {"id": 24, "content":"1111", "create_time": "2023-12-21"},
    {"id": 24, "content":"1111", "create_time": "2023-12-21"},
    {"id": 25, "content":"1111", "create_time": "2024-12-31"}
  ]
}

if client post date: 2023-12-20 and 2023-12-21 will get : the users that create_time beteen 2023-12-20 and 2023-12-21; order by the article number that create_time beteen 2023-12-20 and 2023-12-21;

so, the list data : id: 3 with 4 articles id: 1 with 3 articles id: 2 with 2 articles

without the id:4, because create_time not beteen 2023-12-20 and 2023-12-21

My query like this:

GET /users/_search
{
    "query": {
        "bool": {
            "must": [{
                "range": {
                    "create_time": {
                        "gte": "2023-12-20",
                        "lte": "2023-12-21"
                    }
                }
            }]
        }
    },
    "aggs": {
        "articles": {
            "nested": {
                "path": "articles"
            },
            "aggs": {
                "articles": {
                    "filter": {
                        "bool": {
                            "must": [{
                                "range": {
                                    "articles.create_time": {
                                        "gte": "2023-12-20",
                                        "lte": "2023-12-21",
                                        "format": "yyyy-MM-dd"
                                    }
                                }
                            }]
                        }
                    }
                }
            }
        }
    },
    "size": 0
}

Solution

you need to use reverse_nested query. Here is the your updated query:

GET /users/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "create_time": {
              "gte": "2023-12-20",
              "lte": "2023-12-21"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "articles": {
      "nested": {
        "path": "articles"
      },
      "aggs": {
        "test": {
          "reverse_nested": {},
          "aggs": {
            "unique_count_of_id": {
              "terms": {
                "field": "id"
              },
              "aggs": {
                "article_count": {
                  "nested": {
                    "path": "articles"
                  },
                  "aggs": {
                    "filtered_article_count": {
                      "filter": {
                        "bool": {
                          "must": [
                            {
                              "range": {
                                "articles.create_time": {
                                  "gte": "2023-12-20",
                                  "lte": "2023-12-21",
                                  "format": "yyyy-MM-dd"
                                }
                              }
                            }
                          ]
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}