Have you ever wanted to convert the schema and results passed from the query API of BigQuery into something more acceptable?
There is an endless array of { f: { v: } } nests to contend with sometimes. Well now you can transform it into something simpler!

We are transforming something that looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
{
"schema": {
"fields": [
{
"name": "Name",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Address",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "street",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "city",
"type": "STRING",
"mode": "NULLABLE"
}
]
}
]
},
"rows": [
{
"f": [
{
"v": "Amos"
},
{
"v": [
{
"v": {
"f": [
{
"v": "street1"
},
{
"v": "city1"
}
]
}
},
{
"v": {
"f": [
{
"v": "street2"
},
{
"v": "city2"
}
]
}
}
]
}
]
}
]
}

into this?

1
2
3
4
[ { "Name": "Amos",
"Address":
[ { "Address.street": "street1", "Address.city": "city1" },
{ "Address.street": "street2", "Address.city': "city2" } ] } ]

Now you can, and for some very complicated nested results as well!

See the node.js function below and enjoy!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
function convertBQToMySQLResults(schema, rows) {

var resultRows = []

function recurse (schemaCur, rowsCur, colName) {

if (Array.isArray(schemaCur) && !Array.isArray(result[colName])) {
for(var i=0, l=schemaCur.length; i<l; i++) {
if (colName === "")
recurse(schemaCur[i], rowsCur.f[i], colName + schemaCur[i].name)
else
recurse(schemaCur[i], rowsCur.f[i], colName + "." + schemaCur[i].name)
}
}

if (schemaCur.type && schemaCur.type === "RECORD") {
if (schemaCur.mode !== "REPEATED") {
var valIndex = 0
for (var p in schemaCur.fields) {
if (rowsCur.v === null) {
recurse(schemaCur.fields[p], rowsCur, colName + "." + schemaCur.fields[p].name)
} else {
recurse(schemaCur.fields[p], rowsCur.v.f[valIndex], colName + "." + schemaCur.fields[p].name)
}

valIndex++
}
}

if (schemaCur.mode === "REPEATED") {
result[colName] = []
for (var x in rowsCur.v) {
recurse(schemaCur.fields, rowsCur.v[x], colName)
}
}
} else {
if (schemaCur.mode === "REPEATED") {
if (rowsCur.v !== null) {
result[colName] = rowsCur.v.map( (value, index) => { return value.v })
} else {
result[colName] = [ null ]
}

} else if (Array.isArray(result[colName])) {
let nextRow = {}
for (var j in schemaCur) {
nextRow[colName + "." + schemaCur[j].name] = Array.isArray(rowsCur.v.f[j].v) ? rowsCur.v.f[j].v.map( (value, index) => { return value.v }) : rowsCur.v.f[j].v
}
result[colName].push(nextRow)
} else {
if (colName !== "")
result[colName] = rowsCur.v
}
}
}

for (var r=0, rowsCount=rows.length; r<rowsCount; r++) {
var result = {};
recurse(schema, rows[r], "")
resultRows.push(result)
}

return resultRows
}