BigQuery to find Google Docs In issues

finddocs.sql

SELECT
IF
  (type="IssueCommentEvent",
    REGEXP_EXTRACT(json_value(payload,
        "$.comment.body"), "docs.google.com/document/d/[0-9a-zA-Z_-]*"),
    REGEXP_EXTRACT(json_value(payload,
        "$.issue.body"), "docs.google.com/document/d/[0-9a-zA-Z_-]*") ) AS doc,
  json_value(payload,
    "$.issue.html_url") AS issue_url,
IF
  (type="IssueCommentEvent",
    json_value(payload,
      "$.comment.html_url"),
    "") AS comment_url
FROM
  `githubarchive.month.2021*`
WHERE
  (type="IssuesEvent"
    OR type="IssueCommentEvent")
  AND STARTS_WITH(repo.url, "https://api.github.com/repos/kubernetes")
  AND ((type="IssueCommentEvent"
      AND contains_SUBSTR(json_value(payload,
          "$.comment.body"),
        "docs.google.com/document"))
    OR ( type="IssuesEvent"
      AND contains_SUBSTR(json_value(payload,
          "$.issue.body"),
        "docs.google.com/document") ))
GROUP BY
  doc,
  issue_url,
  comment_url
ORDER BY
  issue_url,
  comment_url,
  doc