如何将指定MySQL查询转换为Elasticsearch查询？

Hey there! Since you're new to Elasticsearch (ES), let’s start with a quick key difference: ES is document-oriented, so it doesn’t handle joins the same way SQL does. The best practice in ES is usually to denormalize your data (embed related fields directly into the ticket document) to optimize query performance—this avoids the need for expensive joins. For this example, I’ll assume you have a denormalized tickets index where fields like customer name, task type, asset name, and ticket source are already embedded (the most common setup for ES). If your data is spread across separate indices, we can adjust later, but let’s start with the standard approach.

Here’s the equivalent ES query for your MySQL statement:

GET /tickets/_search
{
  "size": 0, // We only care about aggregation results, not raw hits
  "query": {
    "bool": {
      "filter": [
        // Match engagement_id exactly
        {"term": {"engagement_id": 14653}},
        // Filter created_time to last 24 hours (ES understands "now-24h" natively)
        {"range": {"created_time": {"gte": "now-24h"}}},
        // Match ticket source name exactly (use .keyword for exact text matches)
        {"term": {"ticket_source_master.name.keyword": "Bus"}}
      ]
    }
  },
  "aggs": {
    "distinct_tasks": {
      // Composite aggregation to get unique combinations of your target fields
      "composite": {
        "size": 10000, // Adjust based on expected number of results
        "sources": [
          {"task_id": {"terms": {"field": "id"}}},
          {"customer": {"terms": {"field": "cust.name.keyword"}}},
          {"task_type": {"terms": {"field": "ticketType.type.keyword"}}},
          {"created_date": {"date_histogram": {"field": "created_time", "calendar_interval": "minute"}}},
          {"asset": {"terms": {"field": "ci.name.keyword"}}}
        ]
      },
      "aggs": {
        // Retrieve the actual field values for each distinct group
        "task_details": {
          "top_hits": {
            "size": 1,
            "_source": ["id", "cust.name", "ticketType.type", "created_time", "ci.name"]
          }
        }
      }
    }
  }
}

Key Explanations: #

• bool.filter: This applies your WHERE clause conditions without affecting scoring (faster than regular queries for filtering).
• .keyword suffix: Used for exact text matches (since text fields in ES are analyzed by default—make sure your index mapping includes these keyword subfields, which most default mappings create automatically).
• Composite Aggregation: Replaces SQL’s SELECT DISTINCT by grouping on all the fields you want to keep unique. The date_histogram for created_time ensures we get precise timestamps (adjust calendar_interval if you need coarser dates, like day).
• Top Hits: Pulls the actual field values for each unique group, making it easy to extract your desired results.

If Your Data Is in Separate Indices: #

If your data is split across ticket, reported_master, ci_master, etc., you’d need to use parent-child relationships or nested objects. But denormalization is strongly recommended for ES performance—joins are far more expensive than in SQL. Let me know if you need help setting up denormalization or cross-index queries!

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