view

Query Example

Insert Incident

INSERT INTO `$PROJECT_ID.itsubaki_fourkeys.incidents` (
  owner,
  repository,
  id,
  description,
  sha,
  resolved_at
)
VALUES (
  'itsubaki',
  'q',
  '1',
  '[TEST] Incident',
  '7b2619e89065d96e683d70a72512e2883c1a2cf6',
  '2021-07-30 13:04:37 UTC'
)

JSON Extract

CREATE TEMP FUNCTION JSON2ARRAY(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
""";

WITH A AS (
SELECT
  owner,
  repository,
  id,
  login,
  type,
  created_at,
  JSON_EXTRACT_SCALAR(raw_payload,'$.head') as head_sha,
  JSON2ARRAY(JSON_EXTRACT(raw_payload,'$.commits')) as commits
FROM `$PROJECT_ID.itsubaki_fourkeys.events`
WHERE type = "PushEvent"
)

SELECT
  owner,
  repository,
  id,
  login,
  type,
  created_at,
  head_sha,
  JSON_EXTRACT_SCALAR(commit,'$.sha') as sha
FROM A, UNNEST(commits) AS commit