如何在BigQuery数据集中使用依赖项查找所有JS项目

Question

我正在尝试编写一个SQL查询，以便在BigQuery中的Github公共数据集中使用特定的依赖项查找所有项目。到目前为止，我在package.json中选择了package.json字段。

SELECT
  repo_name, JSON_QUERY(content, '$.dependencies') as dep 
FROM
  
    (SELECT content, sample_repo_name as repo_name FROM bigquery-public-data.github_repos.sample_contents WHERE id IN (
      SELECT id FROM bigquery-public-data.github_repos.sample_files WHERE RIGHT(path, 12) = "package.json"
    ))
      
LIMIT 10

(我使用示例数据集来限制执行时间)

dep列将包含JSON对象，依赖项作为字段，版本作为该字段的值。

{"autoprefixer-loader":"^2.0.0","babel-core":"5.5.4","babel-eslint":"3.1.9","babel-loader":"5.1.2","babel-runtime":"^5.5.8","compass":"0.1.0","css-loader":"0.12.1","del":"1.1.1","eslint":"0.21.2","eslint-plugin-react":"2.3.0","extract-text-webpack-plugin":"0.8.0","file-loader":"0.8.1","gulp":"3.8.11","gulp-autoprefixer":"2.3.0","gulp-eslint":"0.12.0","gulp-jest":"^0.4.0","gulp-jscs":"1.6.0","gulp-jshint":"1.10.0","gulp-load-plugins":"0.10.0","gulp-plumber":"1.0.1","gulp-react":"3.0.1","gulp-rename":"1.2.2","gulp-ruby-sass":"1.0.5","gulp-run":"1.6.8","gulp-scss-lint":"0.2.0","gulp-sourcemaps":"1.5.2","gulp-template":"3.0.0","gulp-useref":"1.1.2","gulp-util":"3.0.4","harmonize":"^1.4.2","image-webpack-loader":"1.4.0","immutable":"3.7.3","imports-loader":"0.6.3","jasmine-pit":"2.0.2","jasmine-reporters":"1.0.1","jest-cli":"git://github.com/robgietema/jest.git","jshint-stylish":"1.0.2","json-loader":"0.5.2","jsx-loader":"0.13.2","lodash":"3.9.3","marty":"0.10.1","open":"0.0.5","path":"0.11.14","react-hot-loader":"1.2.7","react-router":"0.13.3","react-tools":"0.13.3","run-sequence":"1.1.0","sass-loader":"1.0.2","sc5-styleguide":"^0.3.20","style-loader":"0.12.2","webpack":"1.9.7","webpack-dev-server":"1.8.2","wrench":"1.5.8","yargs":"3.10.0"}

我不知道如何在SQL中查询返回对象的字段。我想写“测试，返回的JSON对象有一个等于fs-extra的字段。

或者，换句话说，我想使用SQL编写Object.keys()函数。

Answer 1

您可以使用一个REGEXP_CONTAINS函数来检查JSON内部是否有一个与fs-extra相等的字段，其中包含一个子查询，然后使用该子查询再次使用JSON_QUERY来提取fs-extra值：

    With d as(SELECT
repo_name, JSON_QUERY(content, '$.dependencies') as dep
FROM
   (SELECT content, sample_repo_name as repo_name FROM bigquery-public-data.github_repos.sample_contents WHERE id IN (
    SELECT id FROM bigquery-public-data.github_repos.sample_files WHERE RIGHT(path, 12) = "package.json"
  ))
)
SELECT d.repo_name, JSON_QUERY(d.dep, '$.fs-extra')as dep FROM d
WHERE REGEXP_CONTAINS(d.dep,'fs-extra')
limit 10

输出：

​

​