How do I join data from different Benchling Applications?

To help you get started with Insights we compiled some joins you will use to combine the data in Benchling Data Warehouse across the different applications.

Registry & Inventory

Let’s say you would like to see where registered entities are located in your inventory.

Tables to consider:
Registry Tables: registry_entity for all registered entities, or schema specific registry tables ie. plasmid
Inventory Tables: container_content and container

Entities in your registry will have a unique identifier in the “id” column. To link them to the container level information join “id” on container_contents “entity_id” column. However, to get the container name as well as container location in plate or box schema you will need to join container_content to container table.

Example Query:
SELECT a.id, b.container_id, c.concentration_si, c.plate_id
FROM benchlingtx.plasmid a
JOIN benchlingtx.container_content b ON a.id=b.entity_id
LEFT JOIN benchlingtx.container c ON b.container_id=c.id

NOTE: The Benchling Warehouse container table will contain all container schemas configured in Inventory Application. (ie. Wells, Tubes, Vials, etc) and plate_id will reference all plate schemas. If your container is placed in a box you will use the box_id column instead.

Requests & Notebook

Let’s say you would like to see all Benchling Requests and any associated Notebook entries created for Fulfillment of those requests.

Tables to consider:
Requests Tables: Request tables are named after the Request Schema configured. ie. protein_request . request_fulfillment tracks the fulfillment actions against a request. request_assignee tracks the user set as the assignee for request.
Notebook Tables: entry

Requests schemas level information lives in the custom request table ie protein_request. Request assignee and fulfillment information is tracked in separate tables which you can join the “request_id” column to the “id” column of the custom Requests table. Finally to get notebook metadata you will join the ‘entry_id’ column of the fulfillment table to the “id” column of the entry table.

Example Query:
SELECT a.id, b.entry_id, c.modified_at::date, b.status, d.user_id AS assignee
FROM protein_request a
LEFT JOIN request_fulfillment b ON a.id=b.request_id
LEFT JOIN entry c ON b.entry_id=c.id
LEFT JOIN request_assignee d ON d.request_id=a.id