8. Finding articles matching a specific affiliation string

Level: Medium

This query requires basic knowledge of SQL and the Dimensions data model

Description

This query returns a list of publications and affiliations associated with a single institution. Each line represents a single affiliation string, and includes the publication ID, the institution ID, and the affiliation string as reported by the journal.

Results are filtered to include only a single institution, specified by GRID ID (grid.69566.3a), and affiliation strings that include the phrase "school of medicine". For more details about working with nested fields, see the tutorial page on the topic.

Query

SELECT
  p.id,
  aff.grid_id,
  aff.raw_affiliation
FROM
  `dimensions-ai.data_analytics.publications` p
CROSS JOIN UNNEST(authors) auth
CROSS JOIN UNNEST(auth.affiliations_address) aff
WHERE
  year = 2020
  AND aff.grid_id = "grid.69566.3a" -- Sendai, Japan
  AND LOWER(aff.raw_affiliation) LIKE "%school of medicine%"

Results

[
  {
    "id": "pub.1120198400",
    "grid_id": "grid.69566.3a",
    "raw_affiliation": "Department of Neurosurgery, Tohoku University Graduate School of Medicine, Sendai, Miyagi, Japan"
  },
  {
    "id": "pub.1117164397",
    "grid_id": "grid.69566.3a",
    "raw_affiliation": "Division of Cardiovascular Surgery, Tohoku University Graduate School of Medicine, 1-1 Seiryo-machi, Aoba-ku, Sendai, Miyagi, Japan."
  },
  {
    "id": "pub.1120113207",
    "grid_id": "grid.69566.3a",
    "raw_affiliation": "Division of Internal Medicine and Hypertension Unit Division of Cardiology, Department of Medical Sciences, University of Torino, Torino Division of Internal Medicine, Department of Medicine, University of Udine, Udine, Italy Division of Clinical Hypertension, Endocrinology and Metabolism, Tohoku University Graduate School of Medicine, Sendai, Japan."
  },
  {
    "id": "pub.1119863526",
    "grid_id": "grid.69566.3a",
    "raw_affiliation": "Division of Emergency and Critical Care Medicine, Tohoku University Graduate School of Medicine, Japan."
  },
  // many thousands more records...
]

8.1 Variant: get unique publication records with affiliation count

SELECT
  COUNT(aff) AS matching_affiliations,
  id,
  title.preferred AS title
FROM
  `dimensions-ai.data_analytics.publications`,
  UNNEST(authors) auth,
  UNNEST(auth.affiliations_address) AS aff
WHERE
  year = 2020
  AND aff.grid_id = "grid.69566.3a"
  AND LOWER(aff.raw_affiliation) LIKE "%school of medicine%"
GROUP BY
  id,
  title

Results from variant

[
  {
    "matching_affiliations": "3",
    "id": "pub.1123153684",
    "title": "Management following endoscopic resection in elderly patients with early‐stage upper gastrointestinal neoplasia"
  },
  {
    "matching_affiliations": "1",
    "id": "pub.1124283456",
    "title": "Unique Sex Steroid Profiles in Estrogen-Producing Adrenocortical Adenoma Associated with Bilateral Hyperaldosteronism"
  },
  {
    "matching_affiliations": "5",
    "id": "pub.1124295695",
    "title": "Clinical implication of myocardial FDG uptake pattern in oncologic PET: retrospective comparison study with stress myocardial perfusion imaging as the reference standard"
  },
  {
    "matching_affiliations": "7",
    "id": "pub.1124238412",
    "title": "Keap1 deletion accelerates mutant K-ras/p53-driven cholangiocarcinoma"
  },
  // many more results...
]