Mechanism of action for drugs

Data Access Google BigQuery/Cloud
1

Hi, is there a way to get the mechanism of action and action type (inhibitor, modulator etc…) for drugs that had been tested againts a disease? I am using the BigQuery example here but I can’t figure out how to join the ’ mechanismOfAction’ data to the query results: https://console.cloud.google.com/bigquery?sq=352646847630:a830ff491e71437596df620bfdbabd42

2

Hello @andrew and welcome to our Community!

To follow a similar logic to your previous BigSQL statement, you can bring the information of the drug’s mechanism of action from the mechanismOfAction table, unnest it, and then keep the rows where drug and target IDs coincide. It is a funny way of joining a table, but I think it does the trick!

Try this and see if it works:

SELECT
  evidence.targetId AS target_id,
  targets.approvedSymbol AS target_symbol,
  evidence.drugId AS drug_id,
  drugs.name AS drug_name,
  moa.actionType as drug_action_type, -- <- the MOA information, you can bring other fields from moa
  drugs.drugType AS drug_type,
  drugs.hasBeenWithdrawn AS drug_withdrawn_warning,
  drugs.blackBoxWarning AS drug_blackbox_warning,
  evidence.clinicalPhase AS clinical_trial_phase,
  evidence.clinicalStatus AS clinical_trial_status,
  studyStartDate AS clinical_trial_start_date,
  studyStopReason AS clinical_trial_stop_reason,
  source_urls.element.niceName AS clinical_trial_reference,
  source_urls.element.url AS clinical_trial_reference_url,
FROM
  `open-targets-prod.platform.evidence` AS evidence,
  UNNEST(evidence.urls.list) AS source_urls,
  `open-targets-prod.platform.mechanismOfAction`AS moa,
  UNNEST(moa.chemblIds.list) AS moa_chemblId,
  UNNEST(moa.targets.list) AS moa_targetId
JOIN
  `open-targets-prod.platform.targets` AS targets
ON
  evidence.targetId=targets.id
JOIN
  `open-targets-prod.platform.molecule` AS drugs
ON
  evidence.drugId=drugs.id
WHERE
  datasourceId="chembl"
  AND diseaseId="EFO_0007416"
  AND moa_chemblId.element = evidence.drugId
  AND moa_targetId.element = evidence.targetId

I’m not any expert in SQL syntax, but I think that to perform a more canonical joining, you’d first need to wrap the unnest within a subquery, and then perform the join.

I hope this helps!
Best,
Irene

1 Like
3

Hi Irene, Thank you so much for your reply. Yes, this seems to work well!