Instantly share code, notes, and snippets.

Embed
What would you like to do?
// 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
);
SELECT taskName, task, tasksCounter, projectID, scripts
FROM getTasks(
SELECT id, JSON_EXTRACT(content, '$.scripts') AS jsonScripts
FROM [package-json-files]
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment