Benchling

Warehouse Tables

Objects from the main Benchling database are periodically synced to the warehouse to make them available for queries. It may take time (usually a few seconds, sometimes a few minutes) for data to appear in the warehouse.

The following standard Benchling tables are synced:

author

A table with all entry authors

batch

A table with all batches

box

A table with all boxes

container

A table with all containers

container_content

A table with all associations between containers and entities/batches, along with the concentration of each sample in each container

container_transfer

A table with all container transfers

entry

A table with all entries

entry_auditor

A table with all entry review auditors

field

A table with all fields for schematized objects

folder

A table with all folders

location

A table with all locations

plate

A table with all plates

project

A table with all projects

registry_entity

A table with all entities that are in the registry. This includes sequences, proteins, and custom entities. The metadata (fields) of entities are stored in the separate field table

request_sample

A table with all samples in requests

request_fulfillment

A table with all executions of requests/tasks into entries/workflows

request_assignee

A table with all request assignees

stage_run

A table with all stage runs of workflows

user

A table with all users

team

A table with all teams

team_member

A table with all associations between users and teams

workflow

A table with all workflows

workflow_template

A table with all workflow templates

workflow_template_version

A table with all versions of each workflow template

schema

A table with all schemas

field_definition

A table with all field definitions

assay_result_schema

A table with all result schemas

assay_run_schema

A table with all run schemas

batch_schema

A table with all batch schemas

box_schema

A table with all box schemas

container_schema

A table with all container schemas

entity_schema

A table with all entity schemas

location_schema

A table with all location schemas

plate_schema

A table with all plate schemas

request_schema

A table with all request schemas

Requests, request tasks, runs, and results are synced to their own tables. The name of the table is derived from the schema name. For example, a run schema called Flow Cytometry Run will have a table called flow_cytometry_run. The table columns are based on the fields of the schema.

Fields

You can query for fields on entities by joining the registry_entity and field tables. For example, to show all resistance field values, you might query:

SELECT
registry_entity.id, registry_entity.name, field.name AS field_name, field.value
FROM registry_entity
JOIN field ON registry_entity.id = field.registry_entity_id
JOIN schema_field ON field.schema_field_id = schema_field.id
WHERE field.name = 'Resistance';

      id      |   name    | field_name | value
--------------+-----------+------------+--------
 seq_diETgMr3 | backBN001 | Resistance | AmpR
 seq_vFYFPvDQ | backBn002 | Resistance | Kan
 seq_6RUIueD4 | backBN003 | Resistance | AmpR
(3 rows)

You can also query for all fields attached to a single entity:

SELECT
registry_entity.id, registry_entity.name, schema_field.name AS field_name, field.value
FROM registry_entity
JOIN field ON registry_entity.id = field.registry_entity_id
JOIN schema_field ON field.schema_field_id = schema_field.id
WHERE registry_entity.id = 'bfi_YdzRyDA6';


      id      |   name   |  field_name   |    value
--------------+----------+---------------+-------------
 bfi_YdzRyDA6 | AMAT1132 | Transgene     | bfi_q11Mnlbg
 bfi_YdzRyDA6 | AMAT1132 | Analytes      | Acetic acid
 (2 rows)

Each row in the field table represents the value of one schematized object's field, for example the value of the "Resistance" field of seq_diETgMr3 in the table above is a single row in this table.

The field table has the following columns:

Column
Type
Description

schema_id

string

The ID of the schema that this object belongs to

field_definition_id

string

The ID of the field that this value is for

field_name

string

The name of the field that this value is for

batch_id

string

The ID of the batch this field-value belongs to

box_id

string

The ID of the box this field-value belongs to

container_id

string

The ID of the container this field-value belongs to

entry_id

string

The ID of the entry this field-value belongs to

location_id

string

The ID of the location this field-value belongs to

plate_id

string

The ID of the plate this field-value belongs to

registry_entity_id

string

The ID of the registry entity this field-value belongs to

request_id

string

The ID of the request this field-value belongs to

request_task_id

string

The ID of the request task this field-value belongs to

result_id

string

The ID of the result this field-value belongs to

run_id

string

The ID of the run this field-value belongs to

display_value

string

The text representation of this field-value.

blob_value

json

If the field type is blob-link, the blob value as JSON {id, name}

bool_value

bool

If the field type is boolean, the boolean value

float_value

float

If the field type is float, the float value

date_value

string

If the field type is date, the value of the field in YYYY-MM-DD format.

datetime_value

string

If the field type is datetime, the value of the field in ISO 8601 format.

integer_value

integer

If the field type is integer, the integer value

json_value

json

If the field type is JSON, the JSON value

linked_batch_id

string

If the field type is batch-link, the ID of the batch this value links to

linked_box_id

string

If the field type is box-link, the ID of the box this value links to

linked_container_id

string

If the field type is container-link, the ID of the container this value links to

linked_entry_id

string

If the field type is entry-link, the ID of the registry entry this value links to

linked_location_id

string

If the field type is location-link, the ID of the location this value links to

linked_plate_id

string

If the field type is plate-link, the ID of the plate this value links to

linked_registry_entity_id

string

If the field type is entity-link, the ID of the registry entity this value links to

linked_result_id

string

If the field type is result-link, the ID of the result this value links to

linked_run_id

string

If the field type is run-link, the ID of the run this value links to

value_index

integer

If the field is multi-link, the value is the index within that list of items (this table has one row per item in that list). If the field is not multi-link, the value is 0.

Registered Entities

Currently, only registered entities are synced. This may change in the future.


Warehouse Tables


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.