I have an Elasticsearch index with two types of records: one for product data (including item_code, color, size, etc.), and another for sales data containing the item_code and related sales information.

Sample Records of both data types

{
  "index": "products",
  "type": "_doc",
  "id": "1",
  "body": {
    "item_code": "ABC123",
    "color": "Blue",
    "size": "Medium",
    "category": "Clothing",
    // Other product-related fields
  }
}
{
  "index": "sales",
  "type": "_doc",
  "id": "101",
  "body": {
    "item_code": "ABC123",
    "sale_date": "2024-01-15",
    "quantity_sold": 5,
    "total_amount": 150.00,
    // Other sales-related fields
  }
}

I need to create a single query to filter and retrieve products based on conditions such as color, size, or other categories. Additionally, I want to fetch all sales data for the filtered products in the same query.

Due to the large volume of product data (millions of records), running two separate queries is not feasible. How can I construct a single Elasticsearch query to efficiently achieve this, considering the size of the dataset?