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
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!
Hi Irene, Thank you so much for your reply. Yes, this seems to work well!