Dump vuln CPEs from NVD

nvd-dump.sql

WITH
  deep AS(
  WITH
    nested AS(
    SELECT
      configurations.nodes AS arr
    FROM
      `red-team-project.bq_nvd.nvd`
    WHERE
      EXISTS (
      SELECT
        nodes.children
      FROM
        UNNEST(configurations.nodes) AS nodes
      WHERE
        EXISTS (
        SELECT
          cpe_match.cpe23Uri
        FROM
          UNNEST(nodes.cpe_match) AS cpe_match
        WHERE
          cpe_match.vulnerable = TRUE)) )
  SELECT
    a.cpe_match AS one
  FROM
    nested
  CROSS JOIN
    UNNEST(arr) a )
SELECT
  b.cpe23Uri
FROM
  deep
CROSS JOIN
  UNNEST(one) AS b