getTasks.js

// The UDF
function getTasks(row, emit) {
  if (row.jsonScripts) {
    x = JSON.parse(row.jsonScripts)
    if (x !== null && typeof x === 'object') {
      tasksCounter = 0
      Object.keys(x).forEach(function(entry) {
        if (validTask(x, entry)) {
          tasksCounter++;
        }
      });
      Object.keys(x).forEach(function(entry) {
        if (validTask(x, entry)) {
          emit({
            taskName: entry,
            task: decodeTaskHelper(x, entry),
            scripts: row.jsonScripts,
            projectID: row.id,
            tasksCounter: tasksCounter
          });
        }
      });
    }
  } 
}

function validTask(x, entry) {
  return decodeTaskHelper(x, entry) != '' && decodeTaskHelper(x, entry) != 'echo "Error: no test specified" && exit 1';
}

function decodeTaskHelper(x, entry) {
    var returnVal = x[entry];
    return typeof returnVal === 'string' ? returnVal : '' + returnVal;
}

bigquery.defineFunction(
  'getTasks',  // Name used to call the function from SQL

  ['id', 'jsonScripts'],  // Input column names

  // JSON representation of the output schema
  [
    {name: 'task', type: 'string'},
    {name: 'scripts', type: 'string'},
    {name: 'taskName', type: 'string'},
    {name: 'projectID', type: 'string'},
    {name: 'tasksCounter', type: 'integer'},
  ],

  getTasks  // The function reference
);

scripts.sql

SELECT taskName, task, tasksCounter, projectID, scripts
FROM getTasks(
  SELECT id, JSON_EXTRACT(content, '$.scripts') AS jsonScripts
  FROM [package-json-files]
)