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
}