17. One-degree citation network for a single publication

Level: Advanced

This query requires a good understanding of SQL and the Dimensions data model

Description

This query generates a basic (incoming) citation network for a single publication (the "root node").

"Level 1" publications in the query are publications that are citing the root publication. "Level 2" publications are citing Level 1 publications. Each row describes a vertex in the network and what year the citation occurred.

Query

WITH level1 AS (
  SELECT "pub.1099396382" as citation_from, citations.id AS citation_to,
    1 AS level, citations.year as citation_year
  FROM `dimensions-ai.data_analytics.publications` p
    CROSS JOIN UNNEST(citations) AS citations
  WHERE p.id="pub.1099396382" -- starting node defined here
),

level2 AS (
  SELECT l.citation_to AS citation_from, citations.id AS citation_to,
    2 AS level, citations.year AS citation_year
  FROM `dimensions-ai.data_analytics.publications` p
    CROSS JOIN UNNEST(citations) as citations, level1 l
  where p.id = l.citation_to
)

SELECT * from level1
UNION ALL
SELECT * from level2

Results

[
  {
    "citation_from": "pub.1084215961",
    "citation_to": "pub.1135701707",
    "level": "2",
    "citation_year": "2021"
  },
  {
    "citation_from": "pub.1084215961",
    "citation_to": "pub.1126671825",
    "level": "2",
    "citation_year": "2020"
  },
  {
    "citation_from": "pub.1084215961",
    "citation_to": "pub.1101037901",
    "level": "2",
    "citation_year": "2018"
  },
  {
    "citation_from": "pub.1084215961",
    "citation_to": "pub.1120764290",
    "level": "2",
    "citation_year": "2019"
  },
  {
    "citation_from": "pub.1084215961",
    "citation_to": "pub.1103943561",
    "level": "2",
    "citation_year": "2018"
  },
  // many more entries here...
]

17.1 Variant: one-degree references network for a single publication

We could use the same approach in order to build a references network (=outgoing citations).

This can be achieved via the publications field references_ids.

WITH level1 AS (
  SELECT "pub.1099396382" as references_from, reference AS reference_to,
    1 AS level
  FROM `dimensions-ai.data_analytics.publications` p
    CROSS JOIN UNNEST(reference_ids) AS reference
  WHERE p.id="pub.1099396382" -- starting node defined here
),

level2 AS (
  SELECT l.reference_to AS reference_from, reference AS reference_to,
    2 AS level
  FROM `dimensions-ai.data_analytics.publications` p
    CROSS JOIN UNNEST(reference_ids) as reference, level1 l
  where p.id = l.reference_to
)

SELECT * from level1
UNION ALL
SELECT * from level2