7. Finding Journals using string matching

Level: Easy

This query is suitable for new users of Dimensions on Google BigQuery

Description

Returns data on how many publications have been published by journals that include a set of characters in the journal's title. Each row contains basic metadata about a single journal, plus a count of total publications indexed by Dimensions.

Query

SELECT COUNT(id) AS pubs,
  journal.id,
  journal.title,
  journal.issn,
  journal.eissn,
  publisher.name
FROM
  `dimensions-ai.data_analytics.publications`
WHERE
  LOWER(journal.title) LIKE '%medicine%'
GROUP BY 2, 3, 4, 5, 6
ORDER BY pubs DESC
LIMIT 20

Breaking it down

WHERE LOWER(journal.title) LIKE '%medicine%' is a trick to do a case-insensitive substring search:

• LIKE '%medicine%' searches for any string that contains the characters medicine, in that order; the % characters indicate any other characters can show up on either side.
• Using LOWER(journal.title) here converts the entire journal title to lowercase before doing the string comparison, which means "Medicine" will be a match, as will "MEDICINE," "medicine," and so on.

Results

[
  {
    "pubs": "168838",
    "id": "jour.1014075",
    "title": "New England Journal of Medicine",
    "issn": "0028-4793",
    "eissn": "1533-4406",
    "name": "Massachusetts Medical Society"
  },
  {
    "pubs": "84137",
    "id": "jour.1011551",
    "title": "Medicine & Science in Sports & Exercise",
    "issn": "0195-9131",
    "eissn": "1530-0315",
    "name": "Wolters Kluwer"
  },
  {
    "pubs": "58776",
    "id": "jour.1017222",
    "title": "Annals of Internal Medicine",
    "issn": "0003-4819",
    "eissn": "1539-3704",
    "name": "American College of Physicians"
  },
  {
    "pubs": "52827",
    "id": "jour.1312267",
    "title": "Journal of the Royal Society of Medicine",
    "issn": "0141-0768",
    "eissn": "1758-1095",
    "name": "SAGE Publications"
  },
  {
    "pubs": "52384",
    "id": "jour.1017256",
    "title": "JAMA Internal Medicine",
    "issn": "2168-6106",
    "eissn": "2168-6114",
    "name": "American Medical Association (AMA)"
  },
  {
    "pubs": "47157",
    "id": "jour.1027092",
    "title": "Experimental Biology and Medicine",
    "issn": "1535-3702",
    "eissn": "1535-3699",
    "name": "SAGE Publications"
  },
  {
    "pubs": "46459",
    "id": "jour.1016342",
    "title": "Critical Care Medicine",
    "issn": "0090-3493",
    "eissn": "1530-0293",
    "name": "Wolters Kluwer"
  },
  {
    "pubs": "37666",
    "id": "jour.1057918",
    "title": "Journal of Molecular Medicine",
    "issn": "0946-2716",
    "eissn": "1432-1440",
    "name": "Springer Nature"
  },
  {
    "pubs": "34891",
    "id": "jour.1017275",
    "title": "Arizona Medicine",
    "issn": "0093-0415",
    "eissn": "1476-2978",
    "name": null
  },
  {
    "pubs": "31166",
    "id": "jour.1014535",
    "title": "The American Journal of Medicine",
    "issn": "0002-9343",
    "eissn": "1555-7162",
    "name": "Elsevier"
  },
  {
    "pubs": "29793",
    "id": "jour.1017863",
    "title": "Oral Surgery Oral Medicine Oral Pathology and Oral Radiology",
    "issn": "2212-4403",
    "eissn": "2212-4411",
    "name": "Elsevier"
  },
  {
    "pubs": "28529",
    "id": "jour.1090935",
    "title": "Annals of Emergency Medicine",
    "issn": "0196-0644",
    "eissn": "1097-6760",
    "name": "Elsevier"
  },
  {
    "pubs": "27453",
    "id": "jour.1077253",
    "title": "Medicine",
    "issn": "0025-7974",
    "eissn": "1536-5964",
    "name": "Wolters Kluwer"
  },
  {
    "pubs": "25713",
    "id": "jour.1017316",
    "title": "Bulletin of Experimental Biology and Medicine",
    "issn": "0007-4888",
    "eissn": "1573-8221",
    "name": "Springer Nature"
  },
  {
    "pubs": "24861",
    "id": "jour.1077126",
    "title": "Journal of Experimental Medicine",
    "issn": "0022-1007",
    "eissn": "1540-9538",
    "name": "Rockefeller University Press"
  },
  {
    "pubs": "24370",
    "id": "jour.1319882",
    "title": "Journal of Internal Medicine",
    "issn": "0954-6820",
    "eissn": "1365-2796",
    "name": "Wiley"
  },
  {
    "pubs": "23679",
    "id": "jour.1017748",
    "title": "Academic Medicine",
    "issn": "1040-2446",
    "eissn": "1938-808X",
    "name": "Wolters Kluwer"
  },
  {
    "pubs": "22723",
    "id": "jour.1017031",
    "title": "American Journal of Respiratory and Critical Care Medicine",
    "issn": "1073-449X",
    "eissn": "1535-4970",
    "name": "American Thoracic Society"
  },
  {
    "pubs": "22340",
    "id": "jour.1036793",
    "title": "Military Medicine",
    "issn": "0026-4075",
    "eissn": "1930-613X",
    "name": "Oxford University Press (OUP)"
  },
  {
    "pubs": "21837",
    "id": "jour.1017021",
    "title": "American Journal of Tropical Medicine and Hygiene",
    "issn": "0002-9637",
    "eissn": "1476-1645",
    "name": "American Society of Tropical Medicine and Hygiene"
  }
]