select_duplicates.js

function extract_package_name(r, emit) {
  var output = Object.assign(r); 
  try{
    var package=JSON.parse(r.content);
    emit(Object.assign({}, r, {name : package.name}));
  }
  catch(ex){
    emit(Object.assign({}, r, {name : 'JSON_ERROR'}));
  }
}

bigquery.defineFunction(
  'extract_package_name', // Name of the function exported to SQL
  ['content', 'copies', 'url', 'id'], // Names of input columns
  [{'name': 'copies', 'type': 'integer'}, 
  {'name' : 'name', 'type' : 'string'}, 
  {'name' : 'url', 'type' : 'string'},
  {'name' : 'id', 'type' : 'string'}],  // Output schema
  extract_package_name // Reference to JavaScript UDF
);

SELECT
  id,
  copies,
  name,
  url
FROM
  extract_package_name(
  SELECT
    contents.id as id,
    CONCAT("https://github.com/", files.repo_name, "/blob/", REGEXP_EXTRACT(files.ref, r"refs/heads/(.*)$"), "/", files.path) AS url,
    contents.content as content,
    contents.copies as copies
  FROM
    [githubdataqueries:NpmStat.package_json_content] AS contents
  INNER JOIN
    [githubdataqueries:NpmStat.package_json_files] AS files
  ON
    files.id = contents.id
  WHERE
    contents.copies > 100 )