list_substances_by_assessment
Find substances with specific safety limits or restrictions for particular populations by filtering risk assessments by population type, assessment type, and dosage thresholds.
Instructions
Find substances from risk assessments (CHEM_ASSESS table) filtered by population,
assessment type, and dosage thresholds. This tool combines two queries:
1. Filters CHEM_ASSESS to find matching HAZARD_IDs
2. Retrieves substances associated with those HAZARD_IDs
This tool enables discovery of substances that have specific safety limits or
restrictions for particular populations, returning the actual substance names
and details directly.
## Example questions it can answer:
### Population-Specific Safety Queries
* "Which substances have safety assessments for children under 3?"
* "Find all assessments that apply to pregnant women"
* "What substances have ADI limits for infants?"
* "Show me assessments for children aged 3-10"
* "Which substances have population-specific restrictions?"
### Dosage Threshold Queries
* "Find all substances with ADI less than 1 mg/kg body weight"
* "Which assessments have very low safe intake limits (< 0.1 mg/kg)?"
* "Show me substances with TDI greater than 10 mg/kg"
* "What substances have high safe intake limits (> 100 mg/kg)?"
* "Find assessments with ADI between 0.5 and 5 mg/kg"
### Assessment Type Queries
* "Which substances have ADI (Acceptable Daily Intake) assessments?"
* "Find all TDI (Tolerable Daily Intake) assessments"
* "Show me ARfD (Acute Reference Dose) assessments"
* "What substances have group assessments?"
### Combined Population + Dosage Queries
* "Find substances with ADI < 1 mg/kg for children"
* "Which assessments for pregnant women have low safe limits?"
* "Show me substances with very restrictive limits for infants (< 0.5 mg/kg)"
* "What substances have high ADI (> 50 mg/kg) for adults?"
### Missing Data Queries
* "Which assessments have no quantitative safe intake limit set?"
* "Find substances where risk assessment exists but no ADI/TDI value is specified"
* "Show me assessments that only have qualitative assessments (no numeric limits)"
### Risk Identification Queries
* "Find all substances that indicate issues for children under 3"
* "Which assessments suggest concerns for specific populations?"
* "Show me substances with population-specific warnings"
Args:
population_text_contains: Optional text search in POPULATIONTEXT field (case-insensitive).
Searches for partial matches in population descriptions.
Examples:
- "children" matches "Consumers - Children", "Consumers - Children 1-2 years", etc.
- "pregnant" matches "Consumers - Pregnant women >= 18 years"
- "infant" matches "Consumers - Infants", "Consumers - Infants 0-6 months"
- "adult" matches "Consumers - Adults", "Consumers - Adults >=18 years"
See "Population Text Reference" section below for complete list of accepted values.
assessment_type: Optional filter by assessment type (case-insensitive partial match).
Examples:
- "ADI" matches "ADI" (Acceptable Daily Intake)
- "TDI" matches "TDI" (Tolerable Daily Intake)
- "ARfD" matches "ARfD" (Acute Reference Dose)
- "group" matches group assessments
risk_value_milli_max: Optional maximum RISKVALUE_MILLI in mg/kg body weight (inclusive, <=).
Use to find substances with low safe limits.
Examples:
- 1.0 for ADI < 1 mg/kg
- 0.1 for very restrictive limits
- 10.0 for moderate limits
risk_value_milli_min: Optional minimum RISKVALUE_MILLI in mg/kg body weight (inclusive, >=).
Use to find substances with high safe limits.
Examples:
- 10.0 for ADI > 10 mg/kg
- 100.0 for very high limits
has_no_risk_value: If True, only return HAZARD_IDs where RISKVALUE IS NULL.
Use to find assessments with no quantitative limit set.
These may have qualitative assessments in the ASSESS field instead.
limit: Maximum number of substances to return (default: 10). This limits the final
substance results after filtering by assessment criteria.
Population Text Reference:
The following are accepted POPULATIONTEXT values in the database. Use partial
matches (case-insensitive) to search. For example, "children" will match
"Consumers - Children", "Consumers - Children 1-2 years", etc.
Consumers:
- Consumers
- Consumers - Children
- Consumers - Infants
- Consumers - Adults
- Consumers - Adult women, pregnant
- Consumers - Adult women, lactating
- Consumers - Pregnant women >= 18 years
- Consumers - Pregnant women >= 25 years
- Consumers - Pregnant women 18-24 years
- Consumers - Lactating women >= 18 years
- Consumers - Lactating women >= 25 years
- Consumers - Lactating women 18-24 years
- Consumers - Premenopausal women
- Consumers - Postmenopausal women
- Consumers - Toddlers
- Consumers - Adolescents
- Consumers - Adults >=18 years
- Consumers - Adults >= 25 years
- Consumers - Adults 18-24 years
- Consumers - Children 11-14 years
- Consumers - Children 10-17 years
- Consumers - Children 11-17 years
- Consumers - Children 12-17 years
- Consumers - Children 15-17 years
- Consumers - Children 1-2 years
- Consumers - Children 1-3 years
- Consumers - Children 1-6 years
- Consumers - Children 1-8 years
- Consumers - Children 1-10 years
- Consumers - Children 3 years
- Consumers - Children 3-9 years
- Consumers - Children 4-6 years
- Consumers - Children 4-9 years
- Consumers - Children 4-10 years
- Consumers - Children 7-8 years
- Consumers - Children 7-9 years
- Consumers - Children 7-10 years
- Consumers - Children 7-11 years
- Consumers - Children 9-10 years
- Consumers - Children 10 years
- Consumers - Infants 0-6 months
- Consumers - Infants 0-12 months
- Consumers - Infants 6-12 months
- Consumers - Infants 7-11 months
Workers and Operators:
- Workers
- Worker - adults
- Operators
- Residents and bystanders
- Residents and bystander - children
Pets:
- Dogs as pet
- Cats as pet
Poultry:
- Poultry
- Chicken for meat production
- Chicken for egg production
- Chicken for egg production - adults
- Chicken for egg production, less than 1 year old
- Chicken broilers, less than 1 year old
- Turkeys
- Turkeys for meat production
- Turkeys for meat production, less than 1 year old
- Turkey for reproduction
- Guinea-fowl
- Bird
Pigs:
- Pigs
- Pigs - less than 1 year old
- Pigs - for reproduction
- Pigs for meat production
- Pigs for meat production - adults
- Pigs for meat production - less than 1 year old
- Pigs for reproduction - adults
Cattle:
- Cattle
- Cattle for meat production
- Cattle for meat production - adults
- Cattle for meat production - less than 1 year old
- Cattle for milk production
- Cattle for milk production - adults
- Cattle for milk production - less than 1 year old
- Cattle for reproduction
- Cattle for reproduction - less than 1 year old
- Young cattle of less than 1 year of age
Sheep and Goats:
- Sheep - unspecified
- Sheep for milk production
- Sheep for meat production
- Goat
Other Animals:
- Fish
- Salmons
- Trouts
- Rabbits
- Rabbits for meat production
- Horse
- Equines
- Rodents
- Ruminants - unspecified
Aquatic and Environmental:
- Aquatic animal not used for food production - unspecified
- Aquatic animal for food production - unspecified
- Aquatic organisms
- Aquatic Plants
- Aquatic Invertebrates
- Aquatic compartment
- Terrestrial Plants
- Soil compartment
- Soil macroorganims - arthropods
- Soil macroorganisms - earthworms
Unspecified Categories:
- Animal not used for food production - unspecified
- Animal for food production - unspecified
Returns:
JSON string containing a DataFrame with substance records. Each record includes
substance identification, classification, and alternative names/E-numbers.
The returned data includes:
- Substance identification: SUB_COM_ID (unique identifier)
- Chemical details: COM_NAME (chemical name), COM_TYPE (single/mixture/botanical/synthetic)
- Classification: SUB_TYPE (substance type qualifier)
- Alternative names: DESCRIPTION (comma-separated synonyms, E-numbers, trade names)
Returns an empty DataFrame (as JSON) if no substances match the criteria.
Joins: CHEM_ASSESS → STUDY (by HAZARD_ID) → COMPONENT (by SUB_COM_ID) → SYNONYM (by SUB_COM_ID)
Returns unique substances (DISTINCT by SUB_COM_ID).
Note: Multiple synonyms per substance are aggregated into a comma-separated list.
Use the search_substance tool to get detailed information about specific substances
from the results, or use get_risk_assessments with HAZARD_IDs for detailed
assessment information.
Example usage:
# Find substances with ADI < 1 mg/kg for children
substances_json = list_substances_by_assessment(
population_text_contains="children",
assessment_type="ADI",
risk_value_milli_max=1.0,
limit=20
)Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| population_text_contains | No | ||
| assessment_type | No | ||
| risk_value_milli_max | No | ||
| risk_value_milli_min | No | ||
| has_no_risk_value | No | ||
| limit | No |
Implementation Reference
- The main handler function implementing the tool logic. It fetches hazard IDs matching assessment filters using query_hazard_ids_by_assessment, then retrieves associated substances via query_substances_by_study, returning JSON serialized DataFrame.
def list_substances_by_assessment( population_text_contains: Optional[str] = None, assessment_type: Optional[str] = None, risk_value_milli_max: Optional[float] = None, risk_value_milli_min: Optional[float] = None, has_no_risk_value: Optional[bool] = None, limit: int = 10, ): """ Find substances from risk assessments (CHEM_ASSESS table) filtered by population, assessment type, and dosage thresholds. This tool combines two queries: 1. Filters CHEM_ASSESS to find matching HAZARD_IDs 2. Retrieves substances associated with those HAZARD_IDs This tool enables discovery of substances that have specific safety limits or restrictions for particular populations, returning the actual substance names and details directly. ## Example questions it can answer: ### Population-Specific Safety Queries * "Which substances have safety assessments for children under 3?" * "Find all assessments that apply to pregnant women" * "What substances have ADI limits for infants?" * "Show me assessments for children aged 3-10" * "Which substances have population-specific restrictions?" ### Dosage Threshold Queries * "Find all substances with ADI less than 1 mg/kg body weight" * "Which assessments have very low safe intake limits (< 0.1 mg/kg)?" * "Show me substances with TDI greater than 10 mg/kg" * "What substances have high safe intake limits (> 100 mg/kg)?" * "Find assessments with ADI between 0.5 and 5 mg/kg" ### Assessment Type Queries * "Which substances have ADI (Acceptable Daily Intake) assessments?" * "Find all TDI (Tolerable Daily Intake) assessments" * "Show me ARfD (Acute Reference Dose) assessments" * "What substances have group assessments?" ### Combined Population + Dosage Queries * "Find substances with ADI < 1 mg/kg for children" * "Which assessments for pregnant women have low safe limits?" * "Show me substances with very restrictive limits for infants (< 0.5 mg/kg)" * "What substances have high ADI (> 50 mg/kg) for adults?" ### Missing Data Queries * "Which assessments have no quantitative safe intake limit set?" * "Find substances where risk assessment exists but no ADI/TDI value is specified" * "Show me assessments that only have qualitative assessments (no numeric limits)" ### Risk Identification Queries * "Find all substances that indicate issues for children under 3" * "Which assessments suggest concerns for specific populations?" * "Show me substances with population-specific warnings" Args: population_text_contains: Optional text search in POPULATIONTEXT field (case-insensitive). Searches for partial matches in population descriptions. Examples: - "children" matches "Consumers - Children", "Consumers - Children 1-2 years", etc. - "pregnant" matches "Consumers - Pregnant women >= 18 years" - "infant" matches "Consumers - Infants", "Consumers - Infants 0-6 months" - "adult" matches "Consumers - Adults", "Consumers - Adults >=18 years" See "Population Text Reference" section below for complete list of accepted values. assessment_type: Optional filter by assessment type (case-insensitive partial match). Examples: - "ADI" matches "ADI" (Acceptable Daily Intake) - "TDI" matches "TDI" (Tolerable Daily Intake) - "ARfD" matches "ARfD" (Acute Reference Dose) - "group" matches group assessments risk_value_milli_max: Optional maximum RISKVALUE_MILLI in mg/kg body weight (inclusive, <=). Use to find substances with low safe limits. Examples: - 1.0 for ADI < 1 mg/kg - 0.1 for very restrictive limits - 10.0 for moderate limits risk_value_milli_min: Optional minimum RISKVALUE_MILLI in mg/kg body weight (inclusive, >=). Use to find substances with high safe limits. Examples: - 10.0 for ADI > 10 mg/kg - 100.0 for very high limits has_no_risk_value: If True, only return HAZARD_IDs where RISKVALUE IS NULL. Use to find assessments with no quantitative limit set. These may have qualitative assessments in the ASSESS field instead. limit: Maximum number of substances to return (default: 10). This limits the final substance results after filtering by assessment criteria. Population Text Reference: The following are accepted POPULATIONTEXT values in the database. Use partial matches (case-insensitive) to search. For example, "children" will match "Consumers - Children", "Consumers - Children 1-2 years", etc. Consumers: - Consumers - Consumers - Children - Consumers - Infants - Consumers - Adults - Consumers - Adult women, pregnant - Consumers - Adult women, lactating - Consumers - Pregnant women >= 18 years - Consumers - Pregnant women >= 25 years - Consumers - Pregnant women 18-24 years - Consumers - Lactating women >= 18 years - Consumers - Lactating women >= 25 years - Consumers - Lactating women 18-24 years - Consumers - Premenopausal women - Consumers - Postmenopausal women - Consumers - Toddlers - Consumers - Adolescents - Consumers - Adults >=18 years - Consumers - Adults >= 25 years - Consumers - Adults 18-24 years - Consumers - Children 11-14 years - Consumers - Children 10-17 years - Consumers - Children 11-17 years - Consumers - Children 12-17 years - Consumers - Children 15-17 years - Consumers - Children 1-2 years - Consumers - Children 1-3 years - Consumers - Children 1-6 years - Consumers - Children 1-8 years - Consumers - Children 1-10 years - Consumers - Children 3 years - Consumers - Children 3-9 years - Consumers - Children 4-6 years - Consumers - Children 4-9 years - Consumers - Children 4-10 years - Consumers - Children 7-8 years - Consumers - Children 7-9 years - Consumers - Children 7-10 years - Consumers - Children 7-11 years - Consumers - Children 9-10 years - Consumers - Children 10 years - Consumers - Infants 0-6 months - Consumers - Infants 0-12 months - Consumers - Infants 6-12 months - Consumers - Infants 7-11 months Workers and Operators: - Workers - Worker - adults - Operators - Residents and bystanders - Residents and bystander - children Pets: - Dogs as pet - Cats as pet Poultry: - Poultry - Chicken for meat production - Chicken for egg production - Chicken for egg production - adults - Chicken for egg production, less than 1 year old - Chicken broilers, less than 1 year old - Turkeys - Turkeys for meat production - Turkeys for meat production, less than 1 year old - Turkey for reproduction - Guinea-fowl - Bird Pigs: - Pigs - Pigs - less than 1 year old - Pigs - for reproduction - Pigs for meat production - Pigs for meat production - adults - Pigs for meat production - less than 1 year old - Pigs for reproduction - adults Cattle: - Cattle - Cattle for meat production - Cattle for meat production - adults - Cattle for meat production - less than 1 year old - Cattle for milk production - Cattle for milk production - adults - Cattle for milk production - less than 1 year old - Cattle for reproduction - Cattle for reproduction - less than 1 year old - Young cattle of less than 1 year of age Sheep and Goats: - Sheep - unspecified - Sheep for milk production - Sheep for meat production - Goat Other Animals: - Fish - Salmons - Trouts - Rabbits - Rabbits for meat production - Horse - Equines - Rodents - Ruminants - unspecified Aquatic and Environmental: - Aquatic animal not used for food production - unspecified - Aquatic animal for food production - unspecified - Aquatic organisms - Aquatic Plants - Aquatic Invertebrates - Aquatic compartment - Terrestrial Plants - Soil compartment - Soil macroorganims - arthropods - Soil macroorganisms - earthworms Unspecified Categories: - Animal not used for food production - unspecified - Animal for food production - unspecified Returns: JSON string containing a DataFrame with substance records. Each record includes substance identification, classification, and alternative names/E-numbers. The returned data includes: - Substance identification: SUB_COM_ID (unique identifier) - Chemical details: COM_NAME (chemical name), COM_TYPE (single/mixture/botanical/synthetic) - Classification: SUB_TYPE (substance type qualifier) - Alternative names: DESCRIPTION (comma-separated synonyms, E-numbers, trade names) Returns an empty DataFrame (as JSON) if no substances match the criteria. Joins: CHEM_ASSESS → STUDY (by HAZARD_ID) → COMPONENT (by SUB_COM_ID) → SYNONYM (by SUB_COM_ID) Returns unique substances (DISTINCT by SUB_COM_ID). Note: Multiple synonyms per substance are aggregated into a comma-separated list. Use the search_substance tool to get detailed information about specific substances from the results, or use get_risk_assessments with HAZARD_IDs for detailed assessment information. Example usage: # Find substances with ADI < 1 mg/kg for children substances_json = list_substances_by_assessment( population_text_contains="children", assessment_type="ADI", risk_value_milli_max=1.0, limit=20 ) """ # Step 1: Get HAZARD_IDs matching the assessment criteria # Use a high limit to ensure we get all matching HAZARD_IDs, then limit substances hazard_ids = query_hazard_ids_by_assessment( population_text_contains=population_text_contains, assessment_type=assessment_type, risk_value_milli_max=risk_value_milli_max, risk_value_milli_min=risk_value_milli_min, has_no_risk_value=has_no_risk_value, limit=None, # Get all matching HAZARD_IDs ) # Step 2: If no HAZARD_IDs found, return empty result if not hazard_ids: return pd.DataFrame().to_json() # Step 3: Get substances from those HAZARD_IDs result = query_substances_by_study( ids=hazard_ids, study_type="hazard", limit=limit, ) # Step 4: Return substances as JSON return result["results"].to_json() - main.py:26-26 (registration)Registers the list_substances_by_assessment tool with the FastMCP server.
mcp.add_tool(list_substances_by_assessment) - main.py:12-12 (registration)Imports the list_substances_by_assessment tool function for registration.
from src.mcp_openfoodtox.tools.list_substances_by_assessment import list_substances_by_assessment