How ContentStore Indexes and Retrieves QuestionSql Pairs in DAT

The DAT ContentStore indexes QuestionSql pairs by embedding the natural language question while storing the full pair as JSON metadata, then retrieves relevant SQL by performing similarity search on the question embeddings and deserializing the stored JSON payload.

The ContentStore component in the junjiem/dat repository provides lightweight in-memory RAG (retrieval-augmented generation) capabilities for semantic storage and retrieval of QuestionSql pairs. Built on LangChain4j embedding stores, it cleanly separates the embedding text (used for vector similarity search) from the stored payload (the serialized QuestionSql pair) to enable fast, accurate retrieval of SQL examples based on natural language questions.

Architecture Overview

The ContentStore manages three distinct data types, each with dedicated embedding stores and metadata tags:

Data type	Content type metadata	Store	API methods
Semantic models	MDL	mdlEmbeddingStore	addMdls, retrieveMdl
Question-Sql pairs	SQL	sqlEmbeddingStore	addSqls, retrieveSql
Word-synonym pairs	SYN	synEmbeddingStore	addSyns, retrieveSyn

The Question-Sql workflow follows two distinct phases: indexing (converting pairs into embeddings and JSON payloads) and retrieval (performing similarity search and deserializing results).

Indexing Question-Sql Pairs

The indexing process is implemented in DefaultContentStore.addSqls(List<QuestionSqlPair>) (DefaultContentStore.java:73-91).

The method performs four critical steps:

1. Extract embedding text – Maps each pair's question to a TextSegment for vectorization
2. Compute embeddings – Generates embedding vectors using the configured embeddingModel
3. Serialize payload – Converts the full QuestionSqlPair to JSON and tags it with SQL_METADATA
4. Persist to store – Adds embeddings and JSON segments to sqlEmbeddingStore

@Override
public List<String> addSqls(List<QuestionSqlPair> sqlPairs) {
    // ① Turn each question into a TextSegment (used for embedding)
    List<TextSegment> embedTextSegments = sqlPairs.stream()
            .map(QuestionSqlPair::getQuestion)
            .map(TextSegment::from)
            .toList();

    // ② Compute embeddings for the questions
    List<Embedding> embeddings = embeddingModel.embedAll(embedTextSegments).content();

    // ③ Serialize each QuestionSqlPair to JSON and tag it with SQL metadata
    List<TextSegment> textSegments = sqlPairs.stream()
            .map(pair -> {
                String json = JSON_MAPPER.writeValueAsString(pair);
                return TextSegment.from(json, SQL_METADATA);
            })
            .collect(Collectors.toList());

    // ④ Store embeddings + JSON-wrapped segments in the SQL embedding store
    return sqlEmbeddingStore.addAll(embeddings, textSegments);
}

The metadata constant SQL_METADATA identifies stored segments as Question-Sql pairs:

private static final Metadata SQL_METADATA =
        Metadata.from(METADATA_CONTENT_TYPE, ContentType.SQL.toString());

This design ensures that similarity search operates on the natural language question while the retrieved payload contains the executable SQL.

Retrieving Question-Sql Pairs

Retrieval is handled by DefaultContentStore.retrieveSql(String) (DefaultContentStore.java:18-26).

The retrieval workflow consists of four stages:

1. Query formation – Converts the input question into a LangChain4j Query object
2. Similarity search – Retrieves the most similar segments from sqlEmbeddingStore using vector similarity
3. Re-ranking – Optionally re-ranks results when rerankMode is enabled
4. Deserialization – Converts the stored JSON payloads back into QuestionSqlPair objects

@Override
public List<QuestionSqlPair> retrieveSql(String question) {
    // ① Build a LangChain4j query from the raw question
    Query query = Query.from(question);

    // ② Retrieve the most similar stored segments
    List<Content> contents = getSqlContentRetriever().retrieve(query);

    // ③ Optional re-ranking (if rerank mode enabled)
    if (rerankMode && !contents.isEmpty()) {
        contents = getSqlContentAggregator()
                .aggregate(Collections.singletonMap(query,
                        Collections.singletonList(contents)));
    }

    // ④ Convert the stored JSON payloads back to QuestionSqlPair objects
    return ContentStoreUtil.contents2QuestionSqlPairs(contents);
}

The content retriever (DefaultContentStore.java:95-99) constructs an EmbeddingStoreContentRetriever that queries the sqlEmbeddingStore:

private ContentRetriever getSqlContentRetriever() {
    return EmbeddingStoreContentRetriever.builder()
            .embeddingStore(sqlEmbeddingStore)
            .embeddingModel(embeddingModel)
            .build();
}

Deserialization logic resides in ContentStoreUtil.toQuestionSqlPairs (ContentStoreUtil.java:55-69):

public static List<QuestionSqlPair> toQuestionSqlPairs(List<TextSegment> textSegments) {
    return textSegments.stream()
            .map(ts -> {
                try {
                    return JSON_MAPPER.readValue(ts.text(), QuestionSqlPair.class);
                } catch (JsonProcessingException e) {
                    return null;   // malformed JSON is ignored
                }
            })
            .filter(Objects::nonNull)
            .collect(Collectors.toList());
}

End-to-End Implementation Example

The following example demonstrates building a ContentStore, indexing Question-Sql pairs, and retrieving relevant SQL for a new question:

// 1️⃣ Create a Content Store (builder omitted for brevity)
ContentStore store = DefaultContentStore.builder()
        .embeddingModel(myEmbeddingModel)
        .mdlEmbeddingStore(myMdlStore)
        .sqlEmbeddingStore(mySqlStore)
        .synEmbeddingStore(mySynStore)
        .docEmbeddingStore(myDocStore)
        .defaultChatModel(myChatModel)
        .build();

// 2️⃣ Index a few Q-SQL pairs
List<QuestionSqlPair> pairs = List.of(
        QuestionSqlPair.from("How many users signed up last month?",
                              "SELECT COUNT(*) FROM users WHERE signup_date >= DATE_TRUNC('month', CURRENT_DATE)"),
        QuestionSqlPair.from("What is the total revenue for 2023?",
                              "SELECT SUM(amount) FROM orders WHERE YEAR(order_date) = 2023")
);
store.addSqls(pairs);

// 3️⃣ Retrieve the most relevant pair for a new question
String userQuestion = "Give me the number of new users this month";
List<QuestionSqlPair> results = store.retrieveSql(userQuestion);

if (!results.isEmpty()) {
    QuestionSqlPair best = results.get(0);
    System.out.println("Best match SQL: " + best.getSql());
}

This implementation separates the vector similarity (handled by LangChain4j embedding stores) from the domain data (JSON-wrapped QuestionSqlPair), enabling semantic search without exact keyword matching.

Key Source Files

File	Purpose	Link
DefaultContentStore.java	Core implementation of the in-memory store, including addSqls and retrieveSql.	https://github.com/junjiem/dat/blob/main/dat-core/src/main/java/ai/dat/core/contentstore/DefaultContentStore.java
ContentStoreUtil.java	Helper utilities that translate between raw Content/TextSegment and domain objects (QuestionSqlPair).	https://github.com/junjiem/dat/blob/main/dat-core/src/main/java/ai/dat/core/contentstore/utils/ContentStoreUtil.java
QuestionSqlPair.java	Simple immutable DTO representing a question and its associated SQL statement.	https://github.com/junjiem/dat/blob/main/dat-core/src/main/java/ai/dat/core/contentstore/data/QuestionSqlPair.java
ContentStore.java	Interface exposing the public API (addSql, addSqls, retrieveSql).	https://github.com/junjiem/dat/blob/main/dat-core/src/main/java/ai/dat/core/contentstore/ContentStore.java

Summary

• ContentStore in the DAT repository provides lightweight RAG capabilities for QuestionSql pairs using LangChain4j embedding stores.
• Indexing serializes the full QuestionSqlPair to JSON while embedding only the natural language question, storing both in sqlEmbeddingStore via addSqls.
• Retrieval embeds the incoming question, performs nearest-neighbor search in sqlEmbeddingStore, optionally re-ranks results, and deserializes the JSON payload back to QuestionSqlPair objects via ContentStoreUtil.
• The architecture cleanly separates vector similarity from domain data, enabling semantic search across natural language questions without requiring exact keyword matches.

Frequently Asked Questions

What embedding model does ContentStore use for QuestionSql pairs?

ContentStore delegates embedding computation to a configurable EmbeddingModel instance passed during construction. The addSqls method calls embeddingModel.embedAll() to generate vectors for the natural language questions, while retrieveSql uses the same model to embed incoming queries for similarity search.

How does ContentStore handle malformed or duplicate QuestionSql entries?

During retrieval, ContentStoreUtil.toQuestionSqlPairs catches JsonProcessingException and returns null for malformed JSON entries, filtering them out via Objects::nonNull before returning the final list. The embedding store itself does not enforce uniqueness constraints, so duplicate questions will result in multiple embeddings stored in sqlEmbeddingStore.

What is the difference between the embedding text and the stored payload in ContentStore?

The embedding text consists solely of the natural language question (extracted via QuestionSqlPair::getQuestion), which is vectorized for similarity search. The stored payload is the complete QuestionSqlPair serialized as JSON, wrapped in a TextSegment with SQL_METADATA tags. This separation allows semantic search on questions while preserving the associated SQL for retrieval.

Can ContentStore retrieve SQL without exact keyword matches?

Yes. Because retrieval relies on vector similarity rather than keyword indexing, ContentStore can return relevant SQL examples even when the query uses different terminology or phrasing from the indexed questions. The EmbeddingStoreContentRetriever performs nearest-neighbor search in the embedding space, enabling semantic matching beyond literal string comparison.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}