giahy2507/AptMQL-Bench

AptMQL-Bench

📄 Paper: [AptMQL-Bench: From Text-to-SQL to Text-to-MQL via Access-Pattern Schema Design and Data-Preserving Migration]()  ·  arXiv: coming soon

AptMQL-Bench is a benchmark for text-to-MQL — the task of translating human-readable requests into executable MongoDB Query Language (MQL) aggregation pipelines. It contains 21 document-oriented databases, 3,181 natural-language requests, and their associated gold MQL queries.

Most existing text-to-MQL resources are built by converting a relational benchmark mechanically: mapping each table to a collection one-to-one, or embedding tables along foreign keys. Both approaches derive the MongoDB schema from relational structure, which produces non-native designs, can silently drop rows during migration, and yields ground-truth queries that grow inefficient as the data scales. AptMQL-Bench takes a different route. It is produced by a conversion pipeline that treats schema design as a first-class step: each document schema is designed from the expected access patterns rather than from foreign-key topology, and every query is rewritten to be MongoDB-native. The pipeline is driven by coding agents with human-in-the-loop verification at each stage, so all 21 databases are migrated from their relational sources without data loss, and the ground-truth MQL stays efficient even as the databases grow large.

The benchmark is built on top of BIRD: it reuses BIRD's databases (10 from the train split, 11 from dev), its natural-language questions (kept verbatim, so intent is expressed independently of the query language), and its gold SQL — and adds, for each question, an equivalent MongoDB collection and aggregation pipeline validated for result-equivalence against the original SQL. AptMQL-Bench's databases are among the most structurally complex of comparable benchmarks, averaging 7.1 collections and roughly 218k documents per database, and its MQL queries are comparatively deep, averaging 4.0 aggregation stages at a nesting depth of 4.8. Text-to-MQL remains challenging: the strongest model evaluated, Claude Opus 4.5, reaches only 57.38% soft execution accuracy without external knowledge and 70.34% with it.

The benchmark ships two things:

1. 1.`aptmqlbench_data.jsonl` — 3,181 question / gold-SQL / gold-MQL examples (this is what the Hugging Face Dataset Viewer renders).
2. 2.`databases/` — the underlying databases as mongodump archives (BSON), so you can restore them locally and actually execute the MQL.

Repository layout

AptMQL-Bench/
├── aptmqlbench_data.jsonl            # the benchmark: 3,181 question/SQL/MQL rows
├── restore.sh                        # restores all dumps into a live MongoDB
├── eval_script.py                    # result-equivalence (Soft-EX) checker
├── requirements.txt                  # Python dependencies
├── prompts/                          # prompts + design guidebook for the conversion pipeline
├── ATTRIBUTION.md                    # source attribution & licensing
└── databases/
    ├── dev/                           # 11 databases
    │   └── <db_id>/
    │       ├── dump/                  # mongodump output → restore with mongorestore
    │       │   ├── <collection>.bson
    │       │   ├── <collection>.metadata.json
    │       │   └── prelude.json
    │       └── collections_description/
    │           └── <collection>.jsonl # per-field schema documentation
    └── train/                         # 10 databases (same structure)

Databases

• —dev — california_schools, card_games, codebase_community, debit_card_specializing, european_football_2, financial, formula_1, student_club, superhero, thrombosis_prediction, toxicology
• —train — beer_factory, cs_semester, food_inspection_2, hockey, mondial_geo, professional_basketball, public_review_platform, restaurant, shooting, works_cycles

The aptmqlbench_data.jsonl schema

Each line is one example (JSON object):

The MQL object has two keys:

{
  "collection": "products",
  "aggregation_pipeline": [
    {"$match": {"productNumber": "CA-1098"}},
    {"$lookup": {"from": "productCostHistory", "localField": "_id",
                 "foreignField": "productId", "as": "costHistory"}},
    {"$unwind": "$costHistory"},
    {"$group": {"_id": null, "avg_standard_cost": {"$avg": "$costHistory.standardCost"}}},
    {"$project": {"_id": 0, "avg_standard_cost": 1}}
  ]
}

Run it as db[collection].aggregate(aggregation_pipeline) against the database named db_id.

Schema documentation. Each collection also has a collections_description/<collection>.jsonl file. Every line documents one field: field_name, field_description, data_type, required, value_description. These are handy as schema context when prompting a model.

How to use the data

The end-to-end flow is: install MongoDB → clone this repo → restore the dumps → load `aptmqlbench_data.jsonl` and run the gold MQL with PyMongo.

1. Install MongoDB

On macOS with Homebrew:

brew tap mongodb/brew
brew install mongodb-community          # the mongod server
brew install mongodb-database-tools     # mongorestore, mongoimport, ...
brew install mongosh                    # optional shell
brew services start mongodb-community   # start mongod on localhost:27017

On Windows or Linux, follow the official MongoDB installation guide.

You also need the Python client (used in step 4):

pip install pymongo

2. Clone the repo

The database dumps are stored with Git LFS, so install it before cloning:

brew install git-lfs # macOS if not already installed
git lfs install
git clone https://huggingface.co/datasets/giahy2507/AptMQL-Bench

3. Load the dumps into Live MongoDB

The repo ships a `restore.sh` script that restores every database (dev + train) into a live MongoDB server, each under its own db_id. Make sure MongoDB is running (default mongodb://localhost:27017), then from the repo root run:

cd AptMQL-Bench
bash restore.sh

Point it at a different server or repo location with environment variables if needed:

MONGO_URI="mongodb://user:pass@host:27017" DATA_ROOT="." bash restore.sh

Notes:

• —--db "$db_id" restores the flat *.bson files in dump/ as collections of a database with that exact name — this is what the db_id field in aptmqlbench_data.jsonl refers to.
• —--drop clears any existing collections first, so the loop is safe to re-run.
• —prelude.json just records the source server/tool versions; mongorestore reads it for information and it needs no special handling.
• —Restore a single database instead: mongorestore --drop --db california_schools AptMQL-Bench/databases/dev/california_schools/dump

Sanity-check what landed:

mongosh --quiet --eval 'db.getMongo().getDBNames()'          # list databases
mongosh california_schools --quiet --eval 'db.getCollectionNames()'

4. Load the JSONL and run the MQL (Python + PyMongo, Extended JSON)

Read aptmqlbench_data.jsonl, then parse each gold pipeline through MongoDB Extended JSON (EJSON) with bson.json_util before executing it. Extended JSON decoding turns constructs like {"$date": ...}, {"$oid": ...}, or {"$numberLong": ...} into the proper BSON types the server expects — routing every pipeline through json_util is safe even when a given pipeline is plain JSON.

from bson import json_util
from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017")

# Load the benchmark. json_util.loads parses each line as Extended JSON, so the
# whole row -- including the nested MQL -- is decoded into native BSON types.
with open("aptmqlbench_data.jsonl") as f:
    examples = [json_util.loads(line) for line in f]

def run_example(ex):
    """Execute the gold MQL for one benchmark row and return the result docs."""
    db = client[ex["db_id"]]                 # database name == db_id
    mql = ex["MQL"]
    collection = mql["collection"]
    pipeline = mql["aggregation_pipeline"]   # already Extended-JSON decoded above
    return list(db[collection].aggregate(pipeline))

ex = examples[0]
print(ex["db_id"], "|", ex["question"])
print("gold MQL result:", run_example(ex))

That is the core loop for evaluation: run your model's predicted pipeline the same way and compare its result set against run_example on the gold MQL. The repo ships `eval_script.py`, the result-equivalence checker used for scoring — its compare_fuzzy function implements the soft execution accuracy (Soft-EX) metric. Install its dependencies with pip install -r requirements.txt.

Citation

If you use AptMQL-Bench, please cite:

@article{aptmqlbench,
  title   = {AptMQL-Bench: From Text-to-SQL to Text-to-MQL via Access-Pattern
            Schema Design and Data-Preserving Migration},
  author  = {others},
  journal = {arXiv preprint},
  year    = {2026},
  url     = {}
}