opengenes_example_queries
Retrieve example SQL queries to explore aging and longevity research data in the OpenGenes database for biomedical analysis.
Instructions
Get a list of example SQL queries
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |
Implementation Reference
- src/opengenes_mcp/server.py:472-630 (handler)The handler function for the "opengenes_example_queries" tool. Returns a list of 27 categorized example SQL queries for querying the OpenGenes database, including best practices for multi-value fields, lifespan ordering, cross-table joins, and comprehensive aging evidence patterns.
def get_example_queries(self) -> List[Dict[str, str]]: """ Get comprehensive example SQL queries with patterns and best practices for the OpenGenes database. Includes examples for: - Multi-value field queries (LIKE with wildcards) - Proper result ordering by effect magnitude - Cross-table joins and analysis - Common research questions and patterns Returns: List of dictionaries containing example queries with descriptions and categories """ examples = [ # Basic gene and lifespan queries with proper ordering { "category": "Lifespan Effects - Ordered by Magnitude", "description": "Genes that increase lifespan, ordered by greatest extension first", "query": "SELECT HGNC, model_organism, effect_on_lifespan, lifespan_percent_change_mean FROM lifespan_change WHERE effect_on_lifespan = 'increases lifespan' AND lifespan_percent_change_mean IS NOT NULL ORDER BY lifespan_percent_change_mean DESC", "key_concept": "Always order lifespan results by magnitude for relevance. Use LIMIT only when user specifically asks for 'top N' or similar" }, { "category": "Lifespan Effects - Ordered by Magnitude", "description": "Genes that decrease lifespan, ordered by greatest reduction first", "query": "SELECT HGNC, model_organism, effect_on_lifespan, lifespan_percent_change_mean FROM lifespan_change WHERE effect_on_lifespan = 'decreases lifespan' AND lifespan_percent_change_mean IS NOT NULL ORDER BY lifespan_percent_change_mean ASC", "key_concept": "Use ASC ordering for lifespan reductions to show largest decreases first. Use LIMIT only when user specifically asks for 'top N' or similar" }, { "category": "Lifespan Effects - Mean vs Maximum", "description": "Show both mean and maximum lifespan changes when user asks about lifespan effects", "query": "SELECT HGNC, model_organism, effect_on_lifespan, lifespan_percent_change_mean, lifespan_percent_change_max, significance_mean, significance_max FROM lifespan_change WHERE effect_on_lifespan = 'increases lifespan' AND (lifespan_percent_change_mean IS NOT NULL OR lifespan_percent_change_max IS NOT NULL) ORDER BY COALESCE(lifespan_percent_change_mean, lifespan_percent_change_max) DESC", "key_concept": "IMPORTANT: When user asks about lifespan effects without specifying mean vs max, show both metrics. Researchers may be interested in either average effects or maximum potential. Use LIMIT only when user specifically asks for 'top N' or similar" }, { "category": "Lifespan Effects - Mean vs Maximum", "description": "Compare mean vs maximum lifespan changes for the same interventions", "query": "SELECT HGNC, model_organism, lifespan_percent_change_mean, lifespan_percent_change_max, (lifespan_percent_change_max - lifespan_percent_change_mean) as max_vs_mean_diff FROM lifespan_change WHERE lifespan_percent_change_mean IS NOT NULL AND lifespan_percent_change_max IS NOT NULL AND effect_on_lifespan = 'increases lifespan' ORDER BY max_vs_mean_diff DESC", "key_concept": "Show the difference between maximum and mean effects to highlight variability in responses. Use LIMIT only when user specifically asks for 'top N' or similar" }, # Multi-value field queries (CRITICAL pattern) { "category": "Multi-Value Fields - LIKE Queries", "description": "Find genes associated with stem cell exhaustion hallmark", "query": "SELECT HGNC, \"hallmarks of aging\" FROM gene_hallmarks WHERE \"hallmarks of aging\" LIKE '%stem cell exhaustion%'", "key_concept": "CRITICAL: Always use LIKE with wildcards for multi-value fields" }, { "category": "Multi-Value Fields - LIKE Queries", "description": "Find interventions that improve cardiovascular system", "query": "SELECT HGNC, intervention_improves, effect_on_lifespan, lifespan_percent_change_mean FROM lifespan_change WHERE intervention_improves LIKE '%cardiovascular system%' ORDER BY lifespan_percent_change_mean DESC", "key_concept": "Use LIKE queries for intervention_improves and intervention_deteriorates columns" }, { "category": "Multi-Value Fields - LIKE Queries", "description": "Find genes affecting mitochondrial function", "query": "SELECT HGNC, \"hallmarks of aging\" FROM gene_hallmarks WHERE \"hallmarks of aging\" LIKE '%mitochondrial%'", "key_concept": "Multi-value hallmarks field requires LIKE pattern matching" }, # Cross-table analysis { "category": "Cross-Table Analysis", "description": "Genes with both experimental lifespan effects and population longevity associations", "query": "SELECT DISTINCT lc.HGNC, lc.effect_on_lifespan, lc.model_organism, la.ethnicity, la.\"study type\" FROM lifespan_change lc INNER JOIN longevity_associations la ON lc.HGNC = la.HGNC WHERE lc.effect_on_lifespan = 'increases lifespan'", "key_concept": "Join tables using HGNC to combine experimental and population data" }, { "category": "Cross-Table Analysis - CRITICAL PATTERN", "description": "COMPREHENSIVE AGING EVIDENCE: For questions asking about 'evidence of gene X and aging', ALWAYS query ALL 4 tables", "query": "SELECT criteria FROM gene_criteria WHERE HGNC = 'PTEN'", "key_concept": "CRITICAL: For comprehensive aging evidence questions (like 'What evidence of the link between X and aging'), you MUST query ALL 4 tables: 1) gene_criteria 2) gene_hallmarks 3) lifespan_change 4) longevity_associations. The longevity_associations table contains crucial human population study data that must be included." }, { "category": "Cross-Table Analysis - HUMAN POPULATION DATA", "description": "ALWAYS include human longevity associations when asked about aging evidence", "query": "SELECT \"polymorphism id\", \"nucleotide substitution\", \"amino acid substitution\", ethnicity, \"study type\" FROM longevity_associations WHERE HGNC = 'PTEN'", "key_concept": "ESSENTIAL: When user asks for aging evidence of a gene, human population studies from longevity_associations table are a key component. Include polymorphism details, ethnicity, and study type." }, { "category": "Cross-Table Analysis", "description": "Genes with lifespan effects and their aging criteria", "query": "SELECT lc.HGNC, lc.effect_on_lifespan, lc.lifespan_percent_change_mean, gc.criteria FROM lifespan_change lc INNER JOIN gene_criteria gc ON lc.HGNC = gc.HGNC WHERE lc.lifespan_percent_change_mean > 20 ORDER BY lc.lifespan_percent_change_mean DESC", "key_concept": "Combine lifespan data with criteria to understand gene classifications" }, # Organism-specific patterns { "category": "Model Organism Studies", "description": "Compare gene effects across mammals vs non-mammals", "query": "SELECT HGNC, model_organism, effect_on_lifespan, lifespan_percent_change_mean FROM lifespan_change WHERE HGNC IN (SELECT HGNC FROM lifespan_change WHERE model_organism IN ('mouse', 'rat', 'rabbit', 'hamster')) AND HGNC IN (SELECT HGNC FROM lifespan_change WHERE model_organism IN ('roundworm Caenorhabditis elegans', 'fly Drosophila melanogaster', 'yeasts')) ORDER BY HGNC, model_organism", "key_concept": "Use subqueries to find genes studied in multiple organism types" }, { "category": "Model Organism Studies", "description": "Mouse studies with significant lifespan changes (both mean and max)", "query": "SELECT HGNC, effect_on_lifespan, lifespan_percent_change_mean, lifespan_percent_change_max, significance_mean, significance_max FROM lifespan_change WHERE model_organism = 'mouse' AND (significance_mean = 1 OR significance_max = 1) AND (lifespan_percent_change_mean IS NOT NULL OR lifespan_percent_change_max IS NOT NULL) ORDER BY COALESCE(ABS(lifespan_percent_change_mean), ABS(lifespan_percent_change_max)) DESC", "key_concept": "Filter by significance and show both mean and max when available, order by absolute change magnitude" }, # Intervention and method analysis { "category": "Intervention Methods", "description": "Compare knockout vs overexpression effects on both mean and maximum lifespan", "query": "SELECT intervention_method, effect_on_lifespan, COUNT(*) as count, AVG(lifespan_percent_change_mean) as avg_mean_change, AVG(lifespan_percent_change_max) as avg_max_change FROM lifespan_change WHERE intervention_method IN ('gene knockout', 'additional copies of a gene in the genome') AND (lifespan_percent_change_mean IS NOT NULL OR lifespan_percent_change_max IS NOT NULL) GROUP BY intervention_method, effect_on_lifespan ORDER BY intervention_method, avg_mean_change DESC", "key_concept": "Group by intervention method and show both mean and maximum lifespan metrics to compare approaches comprehensively" }, # Population genetics patterns { "category": "Population Genetics", "description": "Longevity associations by ethnicity and study type", "query": "SELECT ethnicity, \"study type\", COUNT(*) as association_count FROM longevity_associations WHERE ethnicity != 'n/a' GROUP BY ethnicity, \"study type\" ORDER BY association_count DESC", "key_concept": "Analyze population genetics patterns across ethnicities. No LIMIT needed for aggregate statistics" }, { "category": "Population Genetics", "description": "ALL polymorphisms for specific genes (no LIMIT when user asks about gene polymorphisms)", "query": "SELECT HGNC, \"polymorphism type\", \"polymorphism id\", \"nucleotide substitution\", ethnicity, \"study type\" FROM longevity_associations WHERE HGNC = 'FOXO3'", "key_concept": "When user asks about polymorphisms in a gene, show ALL entries without LIMIT to provide complete information" }, { "category": "Population Genetics - When to use LIMIT", "description": "Top 5 genes with most longevity associations (LIMIT appropriate here)", "query": "SELECT HGNC, COUNT(*) as association_count FROM longevity_associations GROUP BY HGNC ORDER BY association_count DESC LIMIT 5", "key_concept": "Use LIMIT only when user specifically asks for 'top N' results or similar superlative language" }, # Summary and statistical queries { "category": "Summary Statistics", "description": "Top genes by number of experiments across all organisms (use LIMIT only when user asks for 'top N')", "query": "SELECT HGNC, COUNT(*) as experiment_count, COUNT(DISTINCT model_organism) as organism_count FROM lifespan_change WHERE HGNC IS NOT NULL GROUP BY HGNC ORDER BY experiment_count DESC LIMIT 10", "key_concept": "Count experiments and organisms per gene for research breadth. Use LIMIT only when user specifically asks for 'top N' genes, otherwise show all results" }, { "category": "Summary Statistics", "description": "Distribution of lifespan effects by organism (including both mean and max metrics)", "query": "SELECT model_organism, effect_on_lifespan, COUNT(*) as count, AVG(lifespan_percent_change_mean) as avg_mean_change, AVG(lifespan_percent_change_max) as avg_max_change, COUNT(CASE WHEN lifespan_percent_change_mean IS NOT NULL THEN 1 END) as mean_data_points, COUNT(CASE WHEN lifespan_percent_change_max IS NOT NULL THEN 1 END) as max_data_points FROM lifespan_change GROUP BY model_organism, effect_on_lifespan ORDER BY model_organism, count DESC", "key_concept": "Analyze effect distributions across model organisms with both metrics and data availability counts" }, # Advanced pattern examples { "category": "Advanced Patterns", "description": "Genes with multiple aging hallmarks (complex multi-value query)", "query": "SELECT HGNC, \"hallmarks of aging\", (LENGTH(\"hallmarks of aging\") - LENGTH(REPLACE(\"hallmarks of aging\", ',', '')) + 1) as hallmark_count FROM gene_hallmarks WHERE hallmark_count > 3 ORDER BY hallmark_count DESC", "key_concept": "Count comma-separated values in multi-value fields" }, { "category": "Advanced Patterns", "description": "Genes affecting both lifespan and specific biological processes", "query": "SELECT lc.HGNC, lc.effect_on_lifespan, lc.lifespan_percent_change_mean, lc.intervention_improves FROM lifespan_change lc WHERE lc.intervention_improves LIKE '%cardiovascular system%' AND lc.intervention_improves LIKE '%cognitive function%' ORDER BY lc.lifespan_percent_change_mean DESC", "key_concept": "Use multiple LIKE conditions to find genes affecting multiple systems" } ] return examples - src/opengenes_mcp/server.py:170-170 (registration)Registration of the tool named "opengenes_example_queries" (using prefix="opengenes_") in the _register_opengenes_tools method of OpenGenesMCP class, binding it to the get_example_queries handler function.
description = "Query the Opengenes database that contains data about genes involved in longevity, lifespan extension experiments on model organisms, and changes in human and other organisms with aging."