How to find all JS projects using a dependency in BigQuery Github dataset

Question

I am trying to write a SQL query to find all projects using particular dependency in Github public dataset in BigQuery. So far I managed to select dependencies field in 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

(I am using sample datasets to limit execution times)

dep column will contain JSON object with dependencies as a field and version as a value for that field.

{"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"}

I don't know how to query fields of returned object in SQL. I would like to write "test that returned JSON object has a field equal to fs-extra.

Or, put differently, I would like to write Object.keys() function using SQL.

user_16929358 · Answer

You could use a REGEXP_CONTAINS function to check if there is a field equal to fs-extra inside the JSON with a subquery and then use that subquery to use again the JSON_QUERY to only extract fs-extra value:

    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

Output:

How to find all JS projects using a dependency in BigQuery Github dataset

1 Answers