如何构建Elasticsearch查询以获取分组后sale_id关联的全部文档？

Got it, let's break this down step by step to solve your problem. First, let's clarify what your original SQL is doing:

select * from es where sale_id in (select * from ES group by sale_id)

Logically, this SQL would return all documents in your index because the subquery gets every unique sale_id, and the main query filters for documents that match any of those IDs. But I assume your actual goal is to fetch all documents associated with each unique sale_id, or retrieve documents for all unique sale_ids while handling large volumes of IDs. Let's cover both scenarios.

1. First: Get All Unique sale_ids (Even Large Volumes) #

Your current aggregation only returns 10 sale_ids because you set "size": 10 in the terms aggregation. Here's how to get more (or all) unique IDs:

For Moderate Number of Unique IDs (10k+) #

Adjust the size parameter in the terms aggregation (Elasticsearch allows increasing this, though avoid going beyond 100k for performance):

{
  "size": 0,  // We don't need top-level hits, just the aggregation results
  "aggs": {
    "unique_sale_ids": {
      "terms": {
        "field": "sale.sale_id",
        "size": 10000  // Set this to a value larger than your expected unique IDs
      }
    }
  }
}

For Extremely Large Number of Unique IDs #

If you have hundreds of thousands/millions of unique sale_ids, use the composite aggregation to paginate through results (avoids memory overload):

{
  "size": 0,
  "aggs": {
    "unique_sale_ids": {
      "composite": {
        "size": 1000,  // Number of IDs to fetch per page
        "sources": [
          {"sale_id": {"terms": {"field": "sale.sale_id"}}}
        ]
      }
    }
  }
}

After each query, take the after_key from the response and add it to your next query (e.g., "after": {"sale_id": "last_id_from_previous_response"}) to get the next page of IDs. Repeat until no more results are returned.

2. Fetch All Documents for the Collected sale_ids #

Once you have the list of unique sale_ids, use a terms query to retrieve all matching documents:

{
  "query": {
    "terms": {
      "sale.sale_id": ["sale_id_1", "sale_id_2", "sale_id_3"]  // Replace with your collected IDs
    }
  },
  "size": 10000  // Adjust if you need more than 10k documents; use `search_after` for pagination if needed
}

If you have more than 10k documents to fetch, use the search_after parameter with a sort field (like _id or a timestamp) to paginate through results efficiently.

3. Alternative: Fetch Documents Directly in the Aggregation #

If you just want to see a sample of documents per sale_id (instead of all documents at once), use the top_hits sub-aggregation:

{
  "size": 0,
  "aggs": {
    "group_by_sale_id": {
      "terms": {
        "field": "sale.sale_id",
        "size": 10  // Number of unique sale_ids to include
      },
      "aggs": {
        "sale_documents": {
          "top_hits": {
            "size": 100  // Number of documents to return per sale_id
          }
        }
      }
    }
  }
}

This will return up to 100 documents for each of the top 10 sale_ids.

Note on Your Original SQL #

If your actual goal was to fetch only documents where sale_id appears more than once (a common use case for grouping), modify the aggregation to filter for IDs with min_doc_count: 2:

{
  "size": 0,
  "aggs": {
    "duplicate_sale_ids": {
      "terms": {
        "field": "sale.sale_id",
        "size": 10000,
        "min_doc_count": 2  // Only include IDs that appear at least twice
      }
    }
  }
}

Then use those IDs in the terms query to get the duplicate documents.

内容的提问来源于stack exchange，提问作者Sumeet Kore