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:

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:

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:

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:

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.