SQL Lookup

Enrich documents with data from SQL databases using parameterized queries

The SQL Lookup stage enriches documents by querying external SQL databases. It executes parameterized queries using document fields as inputs, joining external structured data with your search results.

Stage Category: APPLY (Enriches documents)

Transformation: N documents → N documents (with SQL data added)

When to Use

Use Case	Description
Customer data enrichment	Join customer details from CRM database
Inventory lookups	Add real-time stock levels to product results
Pricing data	Enrich with current pricing from ERP
Cross-system joins	Combine vector search with relational data

When NOT to Use

Scenario	Recommended Alternative
Simple key-value lookups	document_enrich (collection join)
Read-heavy, cacheable data	Consider pre-indexing in Mixpeek
Real-time transactional queries	Direct database access

Parameters

Parameter	Type	Default	Description
connection_id	string	Required	Database connection identifier
query	string	Required	SQL query with parameter placeholders
parameters	object	{}	Mapping of placeholder names to document fields
output_field	string	sql_result	Dot-path where query results are stored
result_handling	string	first	How to handle multiple rows (first, all, error_if_empty, error_if_multiple)
on_no_results	string	null	Behavior when no rows returned (skip, null, error)
timeout	integer	30	Query timeout in seconds (1-300)
on_error	string	skip	Error handling strategy (skip, remove, raise)

Supported Databases

Database	Connection Type	Notes
PostgreSQL	postgresql	Create the connection as shown in PostgreSQL

A connection to any other provider fails with Connection does not support SQL queries. For Snowflake or another warehouse, use the API Call stage against an endpoint that runs the query. See Join Warehouse Metrics onto Results.

Configuration Examples

Basic Customer Lookup

{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "crm_postgres",
      "query": "SELECT name, email, tier FROM customers WHERE id = $1",
      "parameters": {
        "customer_id": "{{DOC.metadata.customer_id}}"
      },
      "output_field": "customer_data"
    }
  }
}

Query Syntax

Parameter Placeholders

Use $1, $2, and so on for placeholders. The stage sorts the keys of parameters in alphabetical order and binds their values to the placeholders in that order. $1 takes the first key alphabetically.

SELECT * FROM users WHERE status = $1 AND id = $2

With parameters set to {"user_id": "...", "status": "active"}, $1 is status and $2 is user_id. The database binds the values, so they are not concatenated into the query text.

Template Variables

Variable	Description
{{DOC.*}}	Any document field
{{INPUT.*}}	Input parameters from retriever call
{{CONTEXT.*}}	Pipeline context variables

Output Schema

Single Row (default)

{
  "document_id": "doc_123",
  "content": "...",
  "sql_result": {
    "name": "John Doe",
    "email": "[e-mail]",
    "tier": "premium"
  }
}

Multiple Rows

{
  "document_id": "doc_123",
  "content": "...",
  "recent_orders": [
    {"order_id": "ord_1", "status": "delivered", "total": 99.99},
    {"order_id": "ord_2", "status": "shipped", "total": 149.99}
  ]
}

No Results

{
  "document_id": "doc_123",
  "content": "...",
  "sql_result": null
}

Security

SQL queries are parameterized to prevent injection attacks. Never concatenate user input directly into query strings.

Security Feature	Description
Parameterized queries	All parameters are escaped
Connection isolation	Each connection uses dedicated credentials
Read-only option	Configure connections as read-only
Query timeout	Prevent long-running queries

Performance

Metric	Value
Latency	10-100ms (depends on query complexity)
Connection pooling	Automatic
Execution	One query per document, run one after another
Timeout handling	Graceful with null result

For high-volume lookups, ensure your database has appropriate indexes on the queried columns. Consider caching frequently accessed data.

Common Pipeline Patterns

Search + SQL Enrichment

[
  {
    "stage_name": "semantic_search",
    "stage_type": "filter",
    "config": {
      "stage_id": "feature_search",
      "parameters": {
        "searches": [
          { "feature_uri": "mixpeek://text_extractor@v1/multilingual_e5_large_instruct_v1", "query": { "input_mode": "text", "value": "{{INPUT.query}}" }, "top_k": 20 }
        ],
        "final_top_k": 20
      }
    }
  },
  {
    "stage_name": "sql_lookup",
    "stage_type": "apply",
    "config": {
      "stage_id": "sql_lookup",
      "parameters": {
        "connection_id": "product_db",
        "query": "SELECT price, stock FROM products WHERE id = $1",
        "parameters": {
          "id": "{{DOC.metadata.product_id}}"
        },
        "output_field": "product_data"
      }
    }
  },
  {
    "stage_name": "structured_filter",
    "stage_type": "filter",
    "config": {
      "stage_id": "attribute_filter",
      "parameters": {
        "field": "product_data.stock",
        "operator": "gt",
        "value": 0
      }
    }
  }
]

Join Warehouse Metrics onto Results

A creative library often carries an ID such as creative_id that keys a metrics table. The table holds spend, ROAS, and thumb-stop rate. Two stages join that table onto search results. Where the table lives picks the stage.

Table location	Stage
PostgreSQL	sql_lookup with a PostgreSQL connection
Snowflake or another warehouse	api_call against an HTTP endpoint that runs the query

Both stages run after the search stage and add fields to each returned document. They do not change which documents were retrieved or how they scored. A search cannot rank by a joined value. To rank by ROAS, ingest it as document data.

From PostgreSQL

Read creative_id from the document metadata and bind it to $1.

{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "ad_metrics_postgres",
      "query": "SELECT spend, roas, thumbstop_rate FROM creative_performance WHERE creative_id = $1",
      "parameters": {
        "creative_id": "{{DOC.metadata.creative_id}}"
      },
      "output_field": "warehouse_metrics"
    }
  }
}

Each result gains a warehouse_metrics object. A creative with no row gets null, because on_no_results defaults to null.

{
  "document_id": "doc_123",
  "score": 0.91,
  "warehouse_metrics": {
    "spend": 1840.5,
    "roas": 3.2,
    "thumbstop_rate": 0.31
  }
}

From Another Warehouse

Put an HTTP endpoint in front of the warehouse. The endpoint takes the ID in its path and returns one JSON object. The stage reads the token from an organization secret. See API Call.

{
  "stage_name": "api_call",
  "stage_type": "apply",
  "config": {
    "stage_id": "api_call",
    "parameters": {
      "url": "https://metrics.example.com/v1/creatives/{{DOC.metadata.creative_id}}/performance",
      "method": "GET",
      "allowed_domains": ["metrics.example.com"],
      "auth": {
        "type": "bearer",
        "secret_ref": "metrics_api_token"
      },
      "output_field": "warehouse_metrics",
      "timeout": 10,
      "on_error": "skip"
    }
  }
}

The endpoint needs a public address and must return JSON. The stage sends one request per document, one after another. A retriever that returns 20 documents sends 20 requests.

Error Handling

Error	Behavior
Query timeout	Handled per on_error (default: document passes through unchanged)
Connection failure	Handled per on_error (default: skip)
No rows returned	output_field set to null (default on_no_results)
Invalid SQL	Stage fails with error message

Related

• Document Enrich - Collection-based joins
• API Call - REST API enrichment
• JSON Transform - Transform enriched data