Elasticsearch Queries That Never Break in Production

How Compile-Time Validation and SQL Transform Your Elasticsearch Development

When a Field Name Typo Costs $50,000 💸

It’s Friday evening, 6 PM. Your e-commerce platform just deployed a new feature: personalized product recommendations based on customer purchase history. The feature passed code review, unit tests, integration tests, and CI/CD pipeline checks. Everything looked perfect.

Saturday morning, 2 AM. Your phone rings. The on-call engineer reports that the recommendation engine is returning empty results for 100% of users. Revenue from personalized recommendations — typically $25,000 per hour during weekend shopping peaks — has dropped to zero.

The root cause? A seemingly innocent typo buried in a complex Elasticsearch aggregation query:

{
  "aggs": {
    "top_categories": {
      "terms": {
        "field": "customer_purchse_history.category",  // ❌ "purchse" instead of "purchase"
        "size": 10
      },
      "aggs": {
        "avg_rating": {
          "avg": {
            "field": "products.rating"
          }
        }
      }
    }
  }
}

The query executed successfully — Elasticsearch didn’t throw an error. It simply returned empty aggregation buckets because the field customer_purchse_history.category doesn’t exist. The application interpreted this as “no recommendations available” and displayed empty carousels to every customer.

Impact of this single-character typo:

• 🔥 2 hours to identify the issue (aggregations silently returned empty results)
• 💰 $16,000 to $50,000 in lost revenue — (Gartner) Small businesses lose between $8,000 to $25,000 per hour of downtime.
• 📉 15% drop in conversion rate during the incident window
• 😰 Frustrated customers seeing broken recommendation sections
• 🚨 Emergency hotfix deployment at 3 AM to correct the field name
• 📝 Post-mortem report and incident review the following week

Why did this happen?

1. ✅ Code review passed: The reviewer saw customer_purchse_history and assumed it was correct (field names aren’t validated)
2. ✅ Unit tests passed: Tests used mocked data that didn’t reflect production field names
3. ✅ Integration tests passed: Test environment had a typo in both the query AND the test data mapping
4. ✅ CI/CD pipeline passed: No compile-time validation for JSON string queries
5. 🔥 Production failed: Real Elasticsearch index had the correct field name customer_purchase_history

The core problem: String-based queries have no compile-time validation. Field name typos, type mismatches, and structural errors are only discovered at runtime — often in production, under load, with real customers affected.

What if this code simply wouldn’t compile?

TL;DR ⚡

SoftClient4ES is an Elasticsearch client that catches query errors before your code even runs.

Key Benefits:

• ✅ Catch errors at compile-time → Zero production errors from field name mistakes and type mismatches
• ✅ Write SQL, not JSON → 10x faster query development
• ✅ Full type validation → Automatic checking of Scala types vs. SQL types
• ✅ Window functions support → Advanced analytics with SQL syntax
• ✅ Minimal runtime overhead → SQL translated to optimized Elasticsearch DSL (<1ms translation time)

Perfect for: Teams tired of debugging production errors, maintaining complex queries, or onboarding new developers.

Read time: 10 minutes | Skill level: Intermediate

The Hidden Cost of Runtime Errors 📊

Let’s be honest: working with Elasticsearch’s JSON DSL can be challenging. While it’s incredibly powerful, the lack of compile-time validation means errors often slip through to production.

Traditional Approach (Official Clients)

// ✅ Compiles successfully
case class Product(id: String, name: String, price: Double, stock: Int)
val query = """
{
  "query": {
    "bool": {
      "filter": [
        {"term": {"pric": {"value": 100}}}  // ❌ Typo: "pric" instead of "price"
      ]
    }
  },
  "_source": ["id", "nam", "price"]  // ❌ Typo: "nam" instead of "name"
}
"""
client.search[Product](query)  // ✅ Compiles fine

What happens:

1. ✅ Code compiles
2. ✅ Tests pass (if they don’t use real field names)
3. ✅ Code review approved (JSON strings are hard to review)
4. ✅ Deployed to production
5. 🔥 Runtime exception at 3 AM

Average time to discover error: 2–48 hours Average cost per incident: $10,000–$100,000

This isn’t a criticism of the official clients — they’re excellent tools. It’s just the nature of string-based queries.

The SoftClient4ES Approach 🛡️

Same code with compile-time validation:

case class Product(id: String, name: String, price: Double, stock: Int)

// ❌ COMPILATION ERROR - Code won't build
val products = client.searchAs[Product]("""
  SELECT id, nam, pric FROM products WHERE pric > 100
""")
// Compiler error:
// ❌ SQL query does not select the required field: name
// You have selected unknown field "nam", did you mean "name"?
//
// ❌ SQL query does not select the required field: price
// You have selected unknown field "pric", did you mean "price"?

What happens:

1. ❌ Code doesn’t compile
2. 🔍 IDE shows errors immediately
3. ✅ Fix typos in 10 seconds
4. ✅ Deploy with confidence
5. 😴 Sleep peacefully

Time to discover error: 10 seconds Cost: $0

How Compile-Time Validation Works 🔬

SoftClient4ES uses Scala macros to analyze your queries during compilation. This approach shifts error detection from runtime to compile-time, catching issues in your IDE rather than in production.

Step-by-Step Process

// Your code
case class User(id: String, email: String, age: Int)

client.searchAs[User]("SELECT id, email FROM users")

At compile-time:

1. Extract SQL query from the string literal
2. Parse SQL to identify selected fields: [id, email]
3. Inspect case class to find required fields: [id, email, age]
4. Compare and detect missing field: age
5. Generate compilation error with actionable fix

At runtime:

1. Translate SQL to Elasticsearch DSL
2. Execute native DSL query against Elasticsearch
3. Deserialize results to case class instances

Translation overhead: <1ms per query (negligible compared to 10–1000ms+ Elasticsearch execution time)

❌ SQL query does not select the required field: age
Example query:
SELECT id, email, age FROM users
To fix this, either:
  1. Add 'age' to the SELECT clause
  2. Make it Option[Int] in the case class
  3. Provide a default value: age: Int = 0

All of this validation happens during compilation. Zero runtime validation overhead. ⚡

Comprehensive Validation: Beyond Field Names 🎯

SoftClient4ES doesn’t just check field names — it performs comprehensive type and structure validation, including pure SQL validation rules.

Validated Operations

Let’s explore each validation with real examples.

Example 1: Type Compatibility Validation

One of the most subtle bugs: type mismatches between SQL and Scala.

case class Product(
  id: String,
  name: String,
  price: Double,
  stock: Int,           // ❌ Scala type: Int
  createdAt: Instant
)

// ❌ COMPILE ERROR: Type mismatch
val products = client.searchAs[Product]("""
  SELECT
    id,
    name,
    price,
    stock::BIGINT,              -- SQL returns BIGINT (Long in Scala)
    created_at AS createdAt
  FROM products
""")
// Compiler error:
// ❌ Type mismatch for field 'stock'
//
// Expected: Int (Scala type in case class)
// Actual:   Long (SQL BIGINT type)
//
// ✅ Solution 1: Change case class to match SQL type
//    case class Product(..., stock: Long, ...)
//
// ✅ Solution 2: Cast in SQL query
//    SELECT ..., CAST(stock AS INT) AS stock, ...

Why this matters:

Without validation, this code would compile but fail at runtime with cryptic deserialization errors. The compiler catches the mismatch and suggests fixes.

Supported type mappings:

Example 2: Pure SQL Validation — GROUP BY Rules

SoftClient4ES validates pure SQL rules, not just Scala/Elasticsearch compatibility.

case class CategoryStats(
  category: String,
  productName: String,    // ❌ Not aggregated, not in GROUP BY
  totalSales: Double,
  avgPrice: Double
)

// ❌ COMPILE ERROR: Invalid GROUP BY
val stats = client.searchAs[CategoryStats]("""
  SELECT
    category,
    product_name AS productName,
    SUM(sales) AS totalSales,
    AVG(price) AS avgPrice
  FROM products
  GROUP BY category
""")
// Compiler error:
// ❌ SQL validation error: Invalid GROUP BY clause
//
// Problem:
//   Field 'product_name' appears in SELECT but is neither:
//   1. Included in GROUP BY clause
//   2. Used in an aggregate function (SUM, AVG, COUNT, etc.)
//   3. Used in a window function with OVER clause
//
// ✅ Solution 1: Add to GROUP BY
//    GROUP BY category, product_name
//
// ✅ Solution 2: Use aggregate function
//    SELECT category, COUNT(DISTINCT product_name) AS productCount, ...
//
// ✅ Solution 3: Use window function
//    SELECT category, FIRST_VALUE(product_name) OVER (PARTITION BY category) AS productName, ...

This catches classic SQL mistakes at compile-time, preventing runtime errors or incorrect results.

Example 3: Catching Typos with Smart Suggestions

case class Product(
  id: String,
  name: String,
  price: Double,
  stock: Int
)

// ❌ COMPILE ERROR
client.searchAs[Product]("SELECT id, nam, pric, stok FROM products")
// Compiler output:
// ❌ Field 'name' is required but not selected
//    Did you mean "nam" → "name"?
//
// ❌ Field 'price' is required but not selected
//    Did you mean "pric" → "price"?
//
// ❌ Field 'stock' is required but not selected
//    Did you mean "stok" → "stock"?

Levenshtein distance algorithm suggests the closest matching field names, making typos easy to spot and fix.

Example 4: Nested Object Validation

One of the trickiest Elasticsearch issues: nested object deserialization.

case class Address(street: String, city: String, country: String)
case class User(id: String, name: String, address: Address)

// ❌ This compiles with official clients but fails at runtime!
client.searchAs[User]("""
  SELECT id, name, address.street, address.city, address.country
  FROM users
""")

Why it fails:

Elasticsearch returns flat structure:

{
  "id": "u1",
  "name": "Alice",
  "address.street": "123 Main St",    // ❌ Flat
  "address.city": "Paris"
}

Jackson expects nested structure:

{
  "id": "u1",
  "name": "Alice",
  "address": {                         // ✅ Nested
    "street": "123 Main St",
    "city": "Paris"
  }
}

Result: NullPointerException or silent data loss in production.

SoftClient4ES catches this at compile-time:

// ❌ COMPILATION ERROR
client.searchAs[User]("""
  SELECT id, name, address.street, address.city, address.country
  FROM users
""")

// Compiler error:
// ❌ Nested object field 'address' cannot be deserialized correctly.
//
// Problem:
//   You are selecting nested fields individually (address.street, address.city).
//   Elasticsearch returns flat fields, but Jackson needs a structured object.
//
// ✅ Solution 1: Select the entire nested object (recommended)
//    SELECT id, name, address FROM users
//
// ✅ Solution 2: Use UNNEST (if you need to filter or join on nested fields)
//    SELECT id, name, address.street, address.city, address.country
//    FROM users
//    JOIN UNNEST(users.address) AS address
//    WHERE addr.city = 'Paris'

The compiler not only catches the error but teaches you the best practice. This has saved us countless hours of debugging.

Example 5: Nested Collection Validation

Collections require special handling in Elasticsearch.

case class OrderItem(productId: String, quantity: Int, price: Double)
case class Order(id: String, customerId: String, items: List[OrderItem])

// ❌ COMPILATION ERROR
client.searchAs[Order]("""
  SELECT id, customerId, items.productId, items.quantity, items.price
  FROM orders
""")
// Compiler error:
// ❌ Collection field 'items' cannot be deserialized correctly.
//
// Problem:
//   You are selecting nested fields without using UNNEST:
//   items.productId, items.quantity, items.price
//
// Elasticsearch will return flat arrays but Jackson needs structured objects
//
// ✅ Solution 1: Select the entire collection  (recommended for simple queries)
//    SELECT id, customerId, items FROM orders
//
// ✅ Solution 2: Use UNNEST for precise field selection (recommended for complex queries)
//    SELECT id, customerId, items.productId, items.quantity, items.price
//    FROM orders
//    JOIN UNNEST(orders.items) AS item
//
// 📚 Documentation:
// https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html

Example 6: Safe Refactoring

This is where compile-time validation truly shines.

Scenario: You rename a field in your domain model.

Without Compile-Time Validation:

// Week 1: Original model
case class Order(id: String, totalAmount: Double, status: String)

// Week 5: Someone refactors
case class Order(id: String, total: Double, status: String)  // ✏️ Renamed
// Week 6: Production breaks 💥
// 47 queries across 12 services still use 'totalAmount'
// Runtime errors everywhere

Cost: 8 hours of debugging, emergency hotfix, incident report.

With SoftClient4ES:

// Week 5: Refactor the model
case class Order(id: String, total: Double, status: String)  // ✏️ Renamed

// Attempt to compile...
// ❌ COMPILATION ERRORS in 47 places:
//
// OrderService.scala:23: error:
//   SQL query does not select the required field: total
//   You have selected unknown field "totalAmount", did you mean "total"?
//
// ReportingService.scala:45: error:
//   SQL query does not select the required field: total
//   ...

Result:

• ✅ All 47 queries identified before code review
• ✅ Fix all queries in 30 minutes
• ✅ Zero production incidents
• ✅ Confident refactoring

The compiler becomes your safety net, catching every affected query automatically.

📖 Full SQL Validation Documentation

SQL: The Universal Query Language 🌍

Beyond type safety, SoftClient4ES lets you write SQL instead of JSON DSL.

We’re not suggesting SQL is inherently better than DSL — both have their place. But for many common queries, SQL offers significant advantages in readability and maintainability.

A Common Query in Different Forms

Business requirement: “Show me products under $100 with stock, sorted by price.”

Elasticsearch JSON DSL (Official Clients):

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "price": {
              "lt": 100
            }
          }
        },
        {
          "range": {
            "stock": {
              "gt": 0
            }
          }
        }
      ]
    }
  },
  "_source": ["id", "name", "price", "stock"],
  "sort": [
    {
      "price": {
        "order": "asc"
      }
    }
  ],
  "size": 20
}

30+ lines. Nested objects. Easy to make mistakes.

SoftClient4ES SQL:

SELECT id, name, price, stock
FROM products
WHERE price < 100 AND stock > 0
ORDER BY price ASC
LIMIT 20

6 lines. Readable. Maintainable. Familiar.

Same result. 5x faster to write. 10x easier to maintain.

Why SQL Can Help

✅ Universal knowledge: Most developers already know SQL ✅ Self-documenting: Queries explain themselves ✅ Onboarding speed: New developers productive immediately ✅ Cross-team collaboration: Data analysts can write queries ✅ Reduced cognitive load: No context switching between SQL (Postgres) and DSL (Elasticsearch)

That said, we recognize that JSON DSL has advantages too — especially for complex nested queries or when you need fine-grained control. That’s why SoftClient4ES supports both SQL and native DSL.

Window Functions: Advanced Analytics Made Simple 📊

SoftClient4ES supports SQL window functions for sophisticated analytics queries — a feature not available in Elasticsearch’s native SQL API.

What Are Window Functions?

Window functions perform calculations across sets of rows without collapsing the result set (unlike GROUP BY).

Use cases:

• Running totals and moving averages
• First/last values in partitions
• Ranking and percentiles
• Year-over-year comparisons

Example: Product Sales Analysis

Business requirement: “For each product, show monthly sales plus total sales, average price, and first/last sale amounts.”

SQL with Window Functions:

SELECT
  product_id,
  product_name,
  DATE_TRUNC(sale_date, MONTH) AS sale_month,
  SUM(amount) AS monthly_sales,

  -- Window functions (calculated across partitions)
  SUM(amount) OVER (PARTITION BY product_id) AS total_sales,
  AVG(amount) OVER (PARTITION BY product_id) AS avg_sale_amount,
  MIN(amount) OVER (PARTITION BY product_id) AS min_sale_amount,
  MAX(amount) OVER (PARTITION BY product_id) AS max_sale_amount,
  COUNT(*) OVER (PARTITION BY product_id) AS sale_count,
  COUNT(DISTINCT customer_id) OVER (PARTITION BY product_id) AS unique_customers,

  FIRST_VALUE(amount) OVER (
    PARTITION BY product_id, DATE_TRUNC(sale_date, MONTH)
    ORDER BY sale_date ASC
  ) AS first_sale_of_month,

  LAST_VALUE(amount) OVER (
    PARTITION BY product_id, DATE_TRUNC(sale_date, MONTH)
    ORDER BY sale_date ASC
  ) AS last_sale_of_month,

  ARRAY_AGG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date ASC
  ) AS all_sale_amounts

FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY product_id, product_name, DATE_TRUNC(sale_date, MONTH)
ORDER BY product_id, sale_month

Type-safe execution with full validation:

case class ProductSalesAnalysis(
  productId: String,
  productName: String,
  saleMonth: String,
  monthlySales: Double,
  totalSales: Double,
  avgSaleAmount: Double,
  minSaleAmount: Double,
  maxSaleAmount: Double,
  saleCount: Long,              // ✅ Type validated: COUNT returns Long
  uniqueCustomers: Long,        // ✅ Type validated: COUNT DISTINCT returns Long
  firstSaleOfMonth: Double,
  lastSaleOfMonth: Double,
  allSaleAmounts: List[Double]  // ✅ Type validated: ARRAY_AGG returns List
)

val results: Source[ProductSalesAnalysis, NotUsed] =
  client.scrollAs[ProductSalesAnalysis](sqlQuery)

Compile-time validation ensures:

• ✅ All window function fields are present in the case class
• ✅ Types match (e.g., Long for COUNT, Double for AVG, List[Double] for ARRAY_AGG)
• ✅ No typos in field names
• ✅ Correct handling of aggregated vs. window function fields
• ✅ Valid GROUP BY clause (fields not aggregated are in GROUP BY)

Supported Window Functions

Two constraints worth knowing before you reach for these. The ranking windows require ORDER BY inside OVER — ROW_NUMBER() OVER (PARTITION BY d) is rejected at parse time rather than left to fail at execution, because a ranking with no order is not a ranking. And the sample forms of the extended-stats family — STDDEV, STDDEV_SAMP, VARIANCE, VAR_SAMP — need Elasticsearch 7.7+, while the population forms STDDEV_POP and VAR_POP work on 6+. PERCENTILE_DISC accepts the same four syntactic forms as PERCENTILE_CONT, including WITHIN GROUP (ORDER BY column).

How Window Functions Work in SoftClient4ES

When you mix window functions with regular fields, SoftClient4ES executes two queries:

1. Aggregations Query: Computes window function results using Elasticsearch aggregations
2. Main Query: Retrieves document fields (if needed)
3. In-Memory Join: Merges results using partition keys (PARTITION BY fields)

Example execution flow:

SQL Query with Window Functions
         ↓
    ┌────────────────────────────────────┐
    │  Query Analysis & Decomposition    │
    └────────────────────────────────────┘
         ↓                    ↓
    ┌─────────────┐    ┌──────────────────┐
    │ Main Query  │    │ Window Functions │
    │ (Fields)    │    │ Query (Aggs)     │
    └─────────────┘    └──────────────────┘
         ↓                    ↓
    ┌─────────────┐    ┌──────────────────┐
    │ ES Search   │    │ ES Aggregations  │
    └─────────────┘    └──────────────────┘
         ↓                    ↓
    ┌─────────────────────────────────────┐
    │   In-Memory Join (Partition Keys)   │
    └─────────────────────────────────────┘
         ↓
    ┌─────────────────────────────────────┐
    │      Type-Safe Result Set           │
    └─────────────────────────────────────┘

Performance: In-memory join is highly optimized and adds minimal overhead (<10ms for typical queries).

Real-World Window Function Example

Customer Purchase Patterns:

case class CustomerPurchase(
  customerId: String,
  purchaseDate: String,
  amount: Double,
  firstPurchaseAmount: Double,    // First purchase ever
  lastPurchaseAmount: Double,     // Most recent purchase
  totalSpent: Double,             // Lifetime value
  avgPurchaseAmount: Double,      // Average order value
  purchaseCount: Long             // Total orders
)

val patterns: Source[CustomerPurchase, NotUsed] =
  client.scrollAs[CustomerPurchase](
"""
  SELECT
    customer_id AS customerId,
    purchase_date AS purchaseDate,
    amount,

    FIRST_VALUE(amount) OVER (
      PARTITION BY customer_id
      ORDER BY purchase_date ASC
    ) AS firstPurchaseAmount,

    LAST_VALUE(amount) OVER (
      PARTITION BY customer_id
      ORDER BY purchase_date ASC
    ) AS lastPurchaseAmount,

    SUM(amount) OVER (PARTITION BY customer_id) AS totalSpent,
    AVG(amount) OVER (PARTITION BY customer_id) AS avgPurchaseAmount,
    COUNT(*) OVER (PARTITION BY customer_id) AS purchaseCount

  FROM purchases
  WHERE purchase_date >= '2024-01-01'
  ORDER BY customer_id, purchase_date
""")

patterns.runWith(Sink.foreach { purchase =>
  println(s"""
    Customer: ${purchase.customerId}
    Purchase: ${purchase.amount} on ${purchase.purchaseDate}
    First Purchase: ${purchase.firstPurchaseAmount}
    Latest Purchase: ${purchase.lastPurchaseAmount}
    Lifetime Value: ${purchase.totalSpent}
    Average Order: ${purchase.avgPurchaseAmount}
    Total Orders: ${purchase.purchaseCount}
  """)
})

This type of query would be significantly more complex to write and maintain in JSON DSL.

Comparison with Official Elasticsearch Clients 🤔

Let’s be clear: Elasticsearch’s official clients are excellent tools, and we have great respect for the work the Elasticsearch team has done. SoftClient4ES is built on top of these clients and wouldn’t exist without them.

That said, they serve different purposes. Here’s an honest comparison:

Official Java/REST Clients

// Official Elasticsearch Java Client
Query priceFilter = Query.of(q -> q
  .range(r -> r
    .field("price")
    .lt(JsonData.of(100))
  )
);

Query stockFilter = Query.of(q -> q
  .range(r -> r
    .field("stock")
    .gt(JsonData.of(0))
  )
);
Query boolQuery = Query.of(q -> q
  .bool(b -> b
    .filter(priceFilter)
    .filter(stockFilter)
  )
);
SearchRequest searchRequest = SearchRequest.of(s -> s
  .index("products")
  .query(boolQuery)
  .sort(so -> so
    .field(f -> f
      .field("price")
      .order(SortOrder.Asc)
    )
  )
  .size(20)
);
SearchResponse<Product> response = client.search(searchRequest, Product.class);
// Manual result processing
response.hits().hits().forEach(hit -> {
Product product = hit.source();
// Process product...
});

Characteristics:

• ✅ Full control over query construction
• ✅ Type-safe builder pattern with lambdas
• ✅ Excellent documentation
• ✅ Supports all Elasticsearch features
• ✅ Automatic deserialization to typed classes
• ❌ Verbose for simple queries (20+ lines for basic search)
• ❌ No compile-time field validation (typos in field names caught at runtime)
• ❌ Manual result iteration required

Elasticsearch SQL API (via REST)

Elasticsearch does have a SQL API, which is a useful feature:

// Elasticsearch SQL API (official)
QueryRequest sqlRequest = QueryRequest.of(q -> q
  .query("SELECT id, name, price FROM products WHERE price < 100")
  .fetchSize(20)
  .format(SqlFormat.Json)
);

QueryResponse response = client.sql().query(sqlRequest);
// Manual deserialization with Jackson
ObjectMapper mapper = new ObjectMapper();
response.rows().forEach(row -> {
  try {
    // Manual field extraction by index position
    JsonNode idNode = mapper.valueToTree(row.get(0));
    JsonNode nameNode = mapper.valueToTree(row.get(1));
    JsonNode priceNode = mapper.valueToTree(row.get(2));
    String id = idNode.asText();
    String name = nameNode.asText();
    Double price = priceNode.asDouble();
    // Manual object construction
    Product product = new Product(id, name, price, null);
    System.out.println(product);

  } catch (Exception e) {
    // Runtime errors if field types don't match
    e.printStackTrace();
  }
});

Characteristics:

• ✅ SQL syntax
• ✅ Good for simple queries
• ✅ Integrated with Elasticsearch
• ❌ No type safety: Results are List<JsonData> - you access fields by index position
• ❌ No compile-time validation: Typos in field names discovered at runtime
• ❌ Limited SQL features: No window functions, basic aggregations only
• ❌ Nested query limitations: Limited to one level of nesting
• ❌ Manual deserialization: You write the JSON parsing code with try/catch blocks
• ❌ No case class validation: Field mismatches discovered in production
• ❌ Positional access: Fields accessed by index (0, 1, 2…) instead of by name
• ❌ Error-prone: Easy to get wrong index or type conversion

SoftClient4ES: Extended Capabilities

SoftClient4ES extends SQL capabilities beyond what’s available in Elasticsearch’s native SQL API:

// SoftClient4ES
case class Product(id: String, name: String, price: Double, stock: Int)

val products: Source[(Product, ScrollMetrics), NotUsed] = client.scrollAs[Product]("""
  SELECT id, name, price, stock
  FROM products
  WHERE price < 100 AND stock > 0
  ORDER BY price ASC
  LIMIT 20
""", client.defaultScrollConfig)

Characteristics:

• ✅ SQL syntax with extended features (window functions, unlimited nesting, complex aggregations)
• ✅ Full type safety: Automatic case class deserialization
• ✅ Compile-time validation: Errors caught in IDE (fields, types, SQL rules)
• ✅ Type checking: Scala types validated against SQL types
• ✅ Unlimited nested queries: No nesting level limitation (vs. 1 level in ES SQL API)
• ✅ Window functions: Advanced analytics not available in ES SQL API
• ✅ Streaming support via Akka Streams
• ✅ Minimal runtime overhead → SQL translated to optimized Elasticsearch DSL (<1ms translation time)
• ✅ Supports both SQL and JSON DSL for maximum flexibility

Feature Comparison Table

When to Use Each

Official Java Client:

• ✅ Need to use latest Elasticsearch features immediately
• ✅ Working with edge cases not yet covered by SoftClient4ES
• ✅ Building low-level infrastructure libraries

Elasticsearch SQL API:

• ✅ Simple ad-hoc queries for exploration
• ✅ Quick prototyping
• ✅ Basic reporting needs

SoftClient4ES:

• ✅ Application code with type-safe domain models
• ✅ Complex analytics queries (window functions, unlimited nesting)
• ✅ Teams that prioritize compile-time safety
• ✅ Projects with frequent refactoring
• ✅ Onboarding new developers quickly
• ✅ Need both SQL and JSON DSL flexibility

You can use all approaches together! SoftClient4ES is built on top of official clients:

// Type-safe SQL for most queries
client.searchAs[Product]("SELECT id, name, price FROM products WHERE price < 100")

// Native JSON DSL when needed (full support)
client.singleSearch(ElasticQuery(query = """{"query": {"match_all": {}}}""", indices = Seq("products")))
// Official client for cutting-edge features
// (SoftClient4ES provides access to underlying client)

JDBC Driver ✅

SoftClient4ES provides a JDBC Type 4 driver for Elasticsearch — one self-contained JAR per ES major, 6 through 9 — enabling:

✅ Cross-index JOIN — which Elasticsearch’s native SQL API cannot do at all (2 JOINs per query, free in Community)

✅ BI tool integration — Superset (dedicated dialect), DBeaver and DataGrip are tested; Tableau, Power BI and Metabase connect over standard JDBC

✅ Window functions (ROW_NUMBER, RANK, DENSE_RANK, STDDEV/VARIANCE, PERCENTILE_CONT/DISC) and multi-level nesting, handled recursively

✅ Standard JDBC interface for maximum compatibility

✅ SQL validation (runtime validation, since JDBC can’t do compile-time)

This is a significant advantage over Elasticsearch’s native SQL API, which has no cross-index JOIN, limited SQL feature support, and single-level nesting restrictions.

Note: a JDBC driver can’t provide compile-time type safety — it’s a runtime API — but it benefits from everything else: window functions, multi-level nesting, comprehensive SQL validation, and better error messages. The driver is licensed under the Elastic License 2.0 (free to use, sources not public); the core engine remains Apache 2.0.

Performance Deep Dive ⚡

SQL Translation Process

SoftClient4ES translates SQL to Elasticsearch DSL at runtime, not compile-time. Here’s why this is still highly efficient:

Translation Pipeline

SQL Query String
      ↓
┌─────────────────────┐
│  SQL Parser         │  <1ms
│  (ANTLR-based)      │
└─────────────────────┘
      ↓
┌─────────────────────┐
│  AST Generation     │  <0.1ms
└─────────────────────┘
      ↓
┌─────────────────────┐
│  DSL Translation    │  <0.5ms
│  (Optimized)        │
└─────────────────────┘
      ↓
┌─────────────────────┐
│  Elasticsearch DSL  │
└─────────────────────┘
      ↓
┌─────────────────────┐
│  Execute Query      │  10-1000ms+
└─────────────────────┘

Why Runtime Translation is Acceptable

1. Translation is fast: Optimized parser with <1ms overhead
2. ES execution dominates: Network + processing = 10–1000ms+
3. Compile-time validation: Most errors caught before runtime
4. No pre-compilation needed: Simpler deployment and debugging

Comparison with Native DSL

// Native DSL: 0ms translation (already DSL)
val dslQuery = """{"query": {"match": {"name": "Alice"}}}"""
client.singleSearch(ElasticQuery(query = dslQuery, indices = Seq("users")))
// Total time: 0ms translation + 50ms ES = 50ms
// SoftClient4ES SQL: 0.3ms translation
val sqlQuery = """SELECT * FROM users WHERE name = 'Alice'"""
client.searchAsUnchecked[User](sqlQuery)
// Total time: 0.3ms translation + 50ms ES = 50.3ms
// Difference: 0.3ms (0.6% overhead)

Verdict: The <1ms translation overhead is negligible compared to the 10x development speed improvement and zero production errors from compile-time validation.

Real-World Impact 📊

We’ve been using SoftClient4ES in production for over a year. Here’s what we’ve observed:

Development Speed

Before SoftClient4ES:

Write query → Google DSL syntax → Copy from StackOverflow → Modify → Test → Debug → Repeat
Average time per query: 15-30 minutes

After SoftClient4ES:

Write SQL → Compile → Works
Average time per query: 2-5 minutes

Time saved: 10–25 minutes per query Queries per week: 20–50 Annual time saved: 173–1,083 hours

Onboarding Time

Before:

• Week 1: Learn Elasticsearch basics
• Week 2: Master Query DSL syntax
• Week 3: Understand aggregations and nested queries
• Week 4: Finally productive

After:

• Day 1: Write SQL queries (already know SQL)
• Day 2: Productive

Onboarding time: 4 weeks → 2 days ⚡

Production Incidents

Our team (10 developers, 6 months):

These numbers are specific to our use case, but they illustrate the potential impact of compile-time validation.

Getting Started 🚀

1. Add Dependency

libraryDependencies += "app.softnetwork.elastic" %% "softclient4es8-java-client" % "0.21.0"

2. Write Your First Type-Safe Query

import app.softnetwork.elastic.client._

// Initialize client
val client = ElasticClientFactory.create()
// Define domain model
case class Product(id: String, name: String, price: Double, stock: Int)
// Write SQL query with compile-time validation
val products: Source[(Product, ScrollMetrics), NotUsed] = client.scrollAs[Product]("""
  SELECT id, name, price, stock
  FROM products
  WHERE price < 100 AND stock > 0
  ORDER BY price ASC
""", client.defaultScrollConfig)
// Process results
products.runWith(Sink.foreach { case (product, _) =>
  println(s"${product.name}: $${product.price} (${product.stock} in stock)")
})

That’s it. Type-safe. Validated. Production-ready. ✅

3. Try Window Functions

case class SalesAnalysis(
  productId: String,
  saleDate: String,
  amount: Double,
  totalSales: Double,
  avgSaleAmount: Double,
  firstSaleAmount: Double,
  lastSaleAmount: Double
)

val analysis: Source[SalesAnalysis, NotUsed] = client.scrollAs[SalesAnalysis]("""
  SELECT
    product_id AS productId,
    sale_date AS saleDate,
    amount,
    SUM(amount) OVER (PARTITION BY product_id) AS totalSales,
    AVG(amount) OVER (PARTITION BY product_id) AS avgSaleAmount,
    FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS firstSaleAmount,
    LAST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS lastSaleAmount
  FROM sales
  WHERE sale_date >= '2024-01-01'
  ORDER BY product_id, sale_date
""")

Common Questions ❓

Q: Does this replace the official Elasticsearch clients?

A: No, it complements them. SoftClient4ES is built on top of official clients and provides a higher-level, type-safe API. You can use both:

// Type-safe SQL for most queries
client.searchAs[Product]("SELECT id, name, price FROM products WHERE price < 100")

// Native JSON DSL when needed (full support)
client.singleSearch(ElasticQuery(query = """{"query": {"match_all": {}}}""", indices = Seq("products")))

Q: What’s the performance overhead of SQL parsing?

A: Negligible. SQL parsing takes <1ms and happens once per query execution. The generated DSL is identical to hand-written queries. Elasticsearch execution time (10–1000ms+) dominates.

Q: Can I use this with my existing Elasticsearch cluster?

A: Yes! SoftClient4ES works with Elasticsearch 6, 7, 8, and 9. No cluster changes required.

Q: What about SQL injection?

A: Use a PreparedStatement — the JDBC driver implements it, and it is the right tool for binding typed values. Know how it works, though: Elasticsearch has no server-side prepared statements, so the driver substitutes parameters into the SQL string on the client, before parsing. String parameters are escaped, which handles ordinary values safely, but that is not the hard server-side boundary Postgres or MySQL give you. So keep treating untrusted input as untrusted — constrain it at your edge, and allow-list anything that lands in an identifier position such as an index or column name, since escaping only covers literals.

Q: Does this work with Kibana/Grafana?

A: Grafana, yes — via the Arrow Flight SQL server and Grafana’s Flight SQL data source plugin, giving you SQL-based dashboards over Elasticsearch. Kibana, no — Kibana talks to Elasticsearch natively and isn’t a SQL client, so there’s nothing to plug into.

For BI more broadly, the JDBC driver covers it: Superset (dedicated dialect), DBeaver and DataGrip are tested; Tableau, Power BI and Metabase work via standard JDBC. It supports window functions and multi-level nesting — neither of which Elasticsearch’s native SQL API offers.

Q: What if I need a feature not supported by SQL?

A: Use native JSON DSL! SoftClient4ES has full support for JSON DSL queries:

// SQL for common cases
client.searchAsUnchecked[Product]("SELECT * FROM products WHERE price < 100")

// Native JSON DSL for any advanced feature
client.singleSearch(ElasticQuery(query = complexDslQuery, indices = Seq("products")))

The Bottom Line 💡

Working with Elasticsearch is powerful, but it can be challenging. We built SoftClient4ES to address pain points we experienced in our own projects:

✅ Compile-time validation → Catch errors before production ✅ Type safety → Automatic validation of types and structures ✅ SQL validation → Pure SQL rules enforced (GROUP BY, etc.) ✅ SQL syntax → Faster development and easier maintenance ✅ Window functions → Advanced analytics without complex DSL ✅ Unlimited nesting → No restrictions on query complexity ✅ Refactoring safety → Compiler finds all affected queries ✅ Minimal runtime overhead → SQL translated to optimized Elasticsearch DSL (<1ms translation time) ✅ Full flexibility → Supports both SQL and JSON DSL

We’re not claiming SoftClient4ES is perfect or right for every use case. But if you’re tired of debugging production errors, maintaining complex JSON queries, or onboarding new developers, it might be worth a try.

Write queries humans can read. Let the compiler ensure they’re correct.

Ready to Try Compile-Time Validation? 🎯

// Your future: type-safe, validated, production-ready queries
case class Order(
  id: String,
  customerId: String,
  total: Double,
  firstOrderAmount: Double,
  totalSpent: Double,
  orderCount: Long
)

val orders = client.scrollAs[Order]("""
  SELECT
    id,
    customer_id AS customerId,
    total,
    FIRST_VALUE(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS firstOrderAmount,
    SUM(total) OVER (PARTITION BY customer_id) AS totalSpent,
    COUNT(*) OVER (PARTITION BY customer_id) AS orderCount
  FROM orders
  WHERE order_date >= '2024-01-01'
  ORDER BY customer_id, order_date
""")
// ✅ Compiles only if all fields match (names + types)
// ✅ Typos caught immediately
// ✅ Type mismatches caught immediately
// ✅ GROUP BY rules validated
// ✅ Refactoring safe
// ✅ Production ready

Get Started:

• 📖 Documentation: Complete Guide
• 💻 GitHub: SoftClient4ES Repository
• 💬 Discussions: Ask Questions
• 📧 Contact: admin@softnetwork.fr

Have you experienced query-related production incidents? How do you handle Elasticsearch query validation in your projects? We’d love to hear your thoughts and experiences! 👇

Built with ❤️ by the SoftNetwork team