Look up structured data with retrieval UDFs

This documentation page is also available as an interactive notebook. You can launch the notebook in Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the above links.

Create lookup functions that query tables by key—for customer records, product catalogs, or financial data.

Problem

You have structured data—customer records, product catalogs, financial data—and need to look up rows by key values. Common scenarios:

Solution

What’s in this recipe:

Create lookup functions from tables with retrieval_udf
Query by single or multiple keys
Use lookups in computed columns for data enrichment

Use pxt.retrieval_udf(table) to automatically create a function that queries the table by its columns.

Setup

%pip install -qU pixeltable

import pixeltable as pxt

# Create a fresh directory
pxt.drop_dir('lookup_demo', force=True)
pxt.create_dir('lookup_demo')

Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata
Created directory ‘lookup_demo’.
<pixeltable.catalog.dir.Dir at 0x143224e50>

Create a product catalog table

# Create a product catalog
products = pxt.create_table(
    'lookup_demo/products',
    {
        'sku': pxt.String,
        'name': pxt.String,
        'price': pxt.Float,
        'category': pxt.String,
    },
)

products.insert(
    [
        {
            'sku': 'LAPTOP-001',
            'name': 'MacBook Pro 14"',
            'price': 1999.00,
            'category': 'electronics',
        },
        {
            'sku': 'LAPTOP-002',
            'name': 'ThinkPad X1',
            'price': 1499.00,
            'category': 'electronics',
        },
        {
            'sku': 'PHONE-001',
            'name': 'iPhone 15 Pro',
            'price': 999.00,
            'category': 'electronics',
        },
        {
            'sku': 'CHAIR-001',
            'name': 'Ergonomic Office Chair',
            'price': 449.00,
            'category': 'furniture',
        },
        {
            'sku': 'DESK-001',
            'name': 'Standing Desk',
            'price': 699.00,
            'category': 'furniture',
        },
    ]
)

products.collect()

Created table ‘products’.
Inserting rows into `products`: 5 rows [00:00, 502.31 rows/s]
Inserted 5 rows with 0 errors.

Create a lookup function with retrieval_udf

# Create a lookup function that searches by SKU
get_product = pxt.retrieval_udf(
    products,
    name='get_product',
    description='Look up a product by its SKU code',
    parameters=['sku'],  # Only use SKU as the lookup key
    limit=1,  # Return at most 1 result
)
# Check the function signature

# Look up a product by SKU
result = products.select(get_product(sku='LAPTOP-001')).limit(1).collect()

Look up by category (multiple results)

# Create a category lookup (returns multiple products)
get_by_category = pxt.retrieval_udf(
    products,
    name='get_by_category',
    description='Get all products in a category',
    parameters=['category'],
    limit=10,  # Return up to 10 products
)

# Find all electronics
products.select(get_by_category(category='electronics')).limit(
    1
).collect()

Use lookups for data enrichment

# Create an orders table
orders = pxt.create_table(
    'lookup_demo/orders',
    {
        'order_id': pxt.String,
        'product_sku': pxt.String,
        'quantity': pxt.Int,
    },
)

orders.insert(
    [
        {
            'order_id': 'ORD-001',
            'product_sku': 'LAPTOP-001',
            'quantity': 2,
        },
        {
            'order_id': 'ORD-002',
            'product_sku': 'PHONE-001',
            'quantity': 1,
        },
        {
            'order_id': 'ORD-003',
            'product_sku': 'CHAIR-001',
            'quantity': 4,
        },
    ]
)

Created table ‘orders’.
Inserting rows into `orders`: 3 rows [00:00, 1186.28 rows/s]
Inserted 3 rows with 0 errors.
3 rows inserted, 6 values computed.

# Add a computed column that enriches orders with product details
orders.add_computed_column(
    product_info=get_product(sku=orders.product_sku)
)

# View enriched orders
orders.select(
    orders.order_id,
    orders.product_sku,
    orders.quantity,
    orders.product_info,
).collect()

Added 3 column values with 0 errors.

Explanation

retrieval_udf parameters:

Use cases:

Tips:

Use limit=1 for unique key lookups
Specify only needed columns in parameters for cleaner APIs
Add descriptions for LLM tool integration

Welcome to Pixeltable

Tools

Core Concepts

How-To

Look up structured data with retrieval UDFs

Problem

Solution

Setup

Create a product catalog table

Create a lookup function with retrieval_udf

Look up by category (multiple results)

Use lookups for data enrichment

Explanation

See also

​Problem

​Solution

​Setup

​Create a product catalog table

​Create a lookup function with retrieval_udf

​Look up by category (multiple results)

​Use lookups for data enrichment

​Explanation

​See also

Problem

Solution

Setup

Create a product catalog table

Create a lookup function with retrieval_udf

Look up by category (multiple results)

Use lookups for data enrichment

Explanation

See also