# Mechanism of action for drugs

**URL:** <https://community.opentargets.org/t/mechanism-of-action-for-drugs/1161>\
**Category:** Google BigQuery/Cloud\
**Created:** [28 July 2023 05:59 UTC](https://community.opentargets.org/t/mechanism-of-action-for-drugs/1161 "2023-07-28T05:59:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![andrew](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@andrew](https://community.opentargets.org/u/andrew)\
**Post date:** [28 July 2023 05:59 UTC](https://community.opentargets.org/t/mechanism-of-action-for-drugs/1161/1 "2023-07-28T05:59:36Z")

</div>

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](https://console.cloud.google.com/bigquery?sq=352646847630:a830ff491e71437596df620bfdbabd42)

---

<div class="post-metadata">

**Author:** ![irene](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/irene/32/50_2.png) [@irene](https://community.opentargets.org/u/irene)\
**Post date:** [31 July 2023 09:33 UTC](https://community.opentargets.org/t/mechanism-of-action-for-drugs/1161/2 "2023-07-31T09:33:08Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![andrew](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@andrew](https://community.opentargets.org/u/andrew)\
**Post date:** [1 August 2023 04:55 UTC](https://community.opentargets.org/t/mechanism-of-action-for-drugs/1161/3 "2023-08-01T04:55:14Z")

</div>

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