Natural Language to Query Translation Agent

Overview

Translating natural language questions into structured database queries is one of the most impactful applications of LLMs in data systems. Rather than requiring users to learn query syntax — whether SQL, Bleve query DSL, or Elasticsearch JSON — a translation agent accepts plain English questions and produces optimized, executable queries. The challenge lies not just in syntactic translation, but in understanding user intent, classifying query types, scoring confidence, and improving queries semantically before execution.

In a typical AI search application, this agent powers the natural language search interface. A user types “find all documents about machine learning published after 2023” and the agent must decide: Is this a factual lookup, an exploratory search, or a navigational query? Each classification leads to different query construction strategies — a factual query becomes a precise filter, an exploratory query uses broader matching with ranking, and a navigational query looks for specific known documents.

This topic covers the full pipeline from natural language input to optimized query output: intent classification, confidence scoring, query generation with schema awareness, semantic query improvement, and the feedback loops that make the agent better over time. These patterns are essential for building any system that bridges the gap between how humans think about data and how databases retrieve it.

Core Concepts

How It Works

The translation pipeline processes natural language input through several stages, each adding structure and precision to the final query.

Stage 1: Intent Analysis

The incoming text is analyzed for structural cues. Questions starting with “how many” signal aggregation queries. “Show me” or “list” indicate retrieval queries. “Compare” triggers multi-query generation. Temporal markers (“last week,” “since January”) are extracted and normalized to date ranges. Entity references (“the orders table,” “customer records”) are matched against the schema.

Stage 2: Query Plan Construction

Based on the classified intent and extracted entities, the agent builds a query plan. Simple questions produce a single query node. Complex questions produce a directed acyclic graph (DAG) of query operations: filter → aggregate → sort → limit. Each node specifies the query operation, the target fields, and any dependencies on prior nodes’ results.

Stage 3: Query Generation

The query plan is lowered into the target query language. For Bleve queries, this means constructing the appropriate combination of term queries, match queries, date range queries, and boolean queries. For SQL targets, it means generating SELECT statements with proper JOINs, WHERE clauses, and aggregations. The LLM generates the query with the schema injected as context, ensuring field names and types are correct.

Stage 4: Confidence Assessment and Improvement

The generated query is evaluated for confidence. The agent checks: Do all referenced fields exist? Are the data types compatible with the operations? Is the query likely to return a useful result set (not too broad, not too narrow)? If confidence is below threshold, the semantic improvement step kicks in — expanding terms, adjusting filters, or requesting clarification.

Implementation Patterns

Query Classifier

The classifier categorizes user intent using pattern matching combined with LLM-based classification for ambiguous cases.

type QueryType = "factual" | "exploratory" | "navigational";

interface ClassificationResult {
  type: QueryType;
  confidence: number;
  entities: string[];
  temporalRange?: { start: string; end: string };
}

function classifyQuery(input: string): ClassificationResult {
  // Pattern-based fast path
  const factualPatterns = /^(what|who|when|where|how many|how much)\b/i;
  const exploratoryPatterns = /^(show|list|find|search|browse|explore)\b/i;
  const navigationalPatterns = /^(go to|open|navigate|find the)\b/i;

  let type: QueryType = "exploratory"; // default
  let confidence = 0.7;

  if (factualPatterns.test(input)) {
    type = "factual";
    confidence = 0.85;
  } else if (navigationalPatterns.test(input)) {
    type = "navigational";
    confidence = 0.80;
  } else if (exploratoryPatterns.test(input)) {
    type = "exploratory";
    confidence = 0.85;
  }

  // Extract temporal markers
  const temporalRange = extractTemporalRange(input);

  // Extract entity references
  const entities = extractEntities(input);

  return { type, confidence, entities, temporalRange };
}

function extractTemporalRange(
  input: string,
): { start: string; end: string } | undefined {
  const now = new Date();
  const patterns: Record<string, () => { start: Date; end: Date }> = {
    "last week": () => ({
      start: new Date(now.getTime() - 7 * 86400000),
      end: now,
    }),
    "last month": () => ({
      start: new Date(now.getFullYear(), now.getMonth() - 1, 1),
      end: now,
    }),
    "last quarter": () => ({
      start: new Date(now.getFullYear(), now.getMonth() - 3, 1),
      end: now,
    }),
    "this year": () => ({
      start: new Date(now.getFullYear(), 0, 1),
      end: now,
    }),
  };

  for (const [pattern, rangeFn] of Object.entries(patterns)) {
    if (input.toLowerCase().includes(pattern)) {
      const range = rangeFn();
      return {
        start: range.start.toISOString(),
        end: range.end.toISOString(),
      };
    }
  }
  return undefined;
}

Bleve Query Builder

The query builder translates classified intent into Bleve search queries, applying schema-aware field validation and semantic improvements.

interface BleveQuery {
  query: Record<string, unknown>;
  size: number;
  from: number;
  sort?: string[];
  fields?: string[];
  highlight?: { fields: string[] };
}

interface SchemaField {
  name: string;
  type: "text" | "numeric" | "datetime" | "boolean" | "keyword";
  indexed: boolean;
  sortable: boolean;
}

function buildBleveQuery(
  classification: ClassificationResult,
  searchTerms: string,
  schema: SchemaField[],
): { query: BleveQuery; confidence: number } {
  const clauses: Record<string, unknown>[] = [];
  let confidence = classification.confidence;

  // Build text search clause
  const textFields = schema
    .filter((f) => f.type === "text" && f.indexed)
    .map((f) => f.name);

  if (textFields.length === 0) {
    confidence *= 0.5; // No searchable text fields
  }

  // Boost title/name fields higher than body fields
  const boostedQuery = {
    disjuncts: textFields.map((field) => ({
      match: searchTerms,
      field,
      boost: field.includes("title") || field.includes("name") ? 2.0 : 1.0,
    })),
  };
  clauses.push(boostedQuery);

  // Add temporal filter if present
  if (classification.temporalRange) {
    const dateFields = schema.filter((f) => f.type === "datetime");
    if (dateFields.length > 0) {
      clauses.push({
        min: classification.temporalRange.start,
        max: classification.temporalRange.end,
        field: dateFields[0].name,
      });
    } else {
      confidence *= 0.7; // Temporal query but no date fields
    }
  }

  // Validate entity references against schema
  for (const entity of classification.entities) {
    const fieldExists = schema.some(
      (f) => f.name.toLowerCase() === entity.toLowerCase(),
    );
    if (!fieldExists) {
      confidence *= 0.8; // Referenced field not found
    }
  }

  const query: BleveQuery = {
    query: clauses.length === 1 ? clauses[0] : { conjuncts: clauses },
    size: classification.type === "factual" ? 5 : 20,
    from: 0,
    highlight: { fields: textFields.slice(0, 3) },
  };

  // Add sorting based on query type
  if (classification.type === "factual") {
    query.sort = ["-_score"]; // Relevance first
  } else if (classification.type === "exploratory") {
    const dateField = schema.find(
      (f) => f.type === "datetime" && f.sortable,
    );
    if (dateField) {
      query.sort = [`-${dateField.name}`, "-_score"];
    }
  }

  return { query, confidence };
}

Practical Applications

The natural language query agent serves as the primary search interface for non-technical users, translating questions about the dataset into optimized Bleve queries without exposing the underlying query DSL.

Conversational Search Refinement

Users iteratively refine their searches through conversation. An initial broad query (“articles about Go”) gets narrowed through follow-ups (“only from 2024,” “with code examples,” “sorted by relevance”). The agent maintains query state across turns, adding filters incrementally rather than rebuilding from scratch. Each refinement preserves the base query while layering additional constraints.

Cross-Index Query Planning

Questions that span multiple data sources require the agent to decompose the query across indexes. “Find customers who viewed products we discontinued last month” requires querying the product catalog for discontinued items, then querying the analytics index for views of those specific products, then joining on customer IDs. The agent produces a multi-step query plan and executes it sequentially.

Query Explanation and Transparency

For power users who want to understand what query was generated, the agent provides a human-readable explanation alongside the structured query. This serves both as a trust mechanism (users can verify the agent understood their intent) and as a learning tool (users gradually learn the query DSL by seeing the translations).

Common Pitfalls

• Over-literal translation — Translating “find me big orders” as a text search for the word “big” instead of interpreting it as a numeric filter on order amount. The agent must distinguish descriptive adjectives from literal search terms.
• Ignoring schema constraints — Generating queries against fields that don’t exist or using operations incompatible with a field’s type (e.g., numeric range on a text field). Always validate generated queries against the current schema before execution.
• Confidence miscalibration — Assigning high confidence to queries that are actually ambiguous, leading users to trust incorrect results. Calibrate confidence scores against actual result quality through feedback loops.
• Temporal expression ambiguity — “Last Friday” could mean the most recent Friday or the Friday of last week. “Q3” could be calendar Q3 or fiscal Q3. Always resolve ambiguous temporal references in the user’s timezone and clarify fiscal calendar assumptions.
• Query explosion on exploratory searches — Expanding synonyms and related terms too aggressively produces queries that match everything, diluting relevance. Apply expansion conservatively and use boosting rather than inclusion to weight expanded terms lower than original terms.
• Missing negation handling — “Find orders that are NOT cancelled” requires a must_not clause. Many naive implementations miss negation, returning cancelled orders alongside active ones.