> This page is for Developer Documentation.

> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.benchling.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.benchling.com/_mcp/server.

# Multi-Tables

## Overview

Multi-tables are specialized warehouse tables that Benchling automatically creates when your schemas contain fields configured to accept multiple values. These tables store the individual values from multi-select fields in a structured format that enables flexible querying and analysis.

**Important:** Multi-value field data appears in both the original per-schema table and the multi-table. The data is duplicated to the multi-table to provide additional querying flexibility. It is not moved or removed from the original table.

## When Multi-Tables Are Created

Multi-tables are only created for the following schema types when they contain multi-select fields:

* Request Task Schemas
* Request Schemas
* Result Schemas
* Run Schemas

Other schema types (such as Entity Schemas, Workflow Task Schemas, etc.) can have multi-select fields configured but multi-tables are not created for them in the warehouse.

## Identifying Multi-Tables

Multi-tables are easy to identify by their naming convention:

**Naming Pattern:** `<schema_name>_multi$raw` (raw table) and `<schema_name>_multi` (view)

**Example:**

* Schema system name: `protein_analysis`
* Standard table: `protein_analysis$raw`
* Multi-table: `protein_analysis_multi$raw`

All tables ending in `_multi` or `_multi$raw` are multi-tables.

## Multi-Table Structure

### Primary Key

Unlike standard warehouse tables that use `id` as the primary key, multi-tables use a composite primary key consisting of:

* `id`: The identifier of the parent record
* `index`: A sequential number (starting from 0) representing the position of each value in the multi-select field

### Schema

Each row in a multi-table represents one value from a multi-select field for a specific parent record.

**Example Structure:**

```sql
protein_analysis_multi$raw
--------------------------
id          | index | field_name        | value
-----------+-------+-------------------+-------------
prt_ABC123 | 0     | sample_types      | Blood
prt_ABC123 | 1     | sample_types      | Serum
prt_XYZ789 | 0     | sample_types      | Plasma
```

In this example, record `prt_ABC123` has two sample types (Blood and Serum), while record `prt_XYZ789` has one (Plasma).

## Querying Multi-Tables

### Basic Query

To retrieve all values for multi-select fields:

```sql
SELECT id, index, field_name, value
FROM protein_analysis_multi
ORDER BY id, index;
```

### Joining with Parent Table

To combine data from the parent table with multi-select values:

```sql
SELECT 
    p.id,
    p.name,
    m.value as sample_type
FROM protein_analysis p
LEFT JOIN protein_analysis_multi m 
    ON p.id = m.id
WHERE m.field_name = 'sample_types';
```

### Aggregating Multi-Select Values

To concatenate multiple values back into a single row:

```sql
SELECT 
    p.id,
    p.name,
    STRING_AGG(m.value, ', ' ORDER BY m.index) as all_sample_types
FROM protein_analysis p
LEFT JOIN protein_analysis_multi m 
    ON p.id = m.id AND m.field_name = 'sample_types'
GROUP BY p.id, p.name;
```

## Need Help?

If you have questions about multi-tables or need assistance querying your warehouse data, please contact Benchling Support or consult with your Benchling Implementation Manager.