Building key-value pairs from field/value row nests
La Sagrada Familia, Barcelona, by Paolo Nicolello.
Say it with me: “Nested JSON is hard to work with!”. Am I right? Most certainly! Now that we got that out of the way, let me just say that I believe in JSON through-and-through. It is logical, it is universal and most languages use it to create fast-access hash-map style data structures. A win for all!
Until you nest it.
In steps a horde of believers in the benefits of nested JSON and those that believe in flattened JSON as their chosen chalice of API payload bliss. So fierce is this silent battle that many flattening techniques litter the repositories of hackers galore, with methods like the recursive way and the non-recursive way.
Flying the nest, recursively
Ever watched the movie Inception? It’s a goodie. A timeline within a timeline within a timeline. And when it all rolls back you see how things fit together. In similar fashion, recursion has a very small footprint in code, but can address huge computational (read “nested”) complexity.
Okay, enough riff-raff, let’s get to it!
BigQuery’s query API return JSON
This is a sample of Google BigQuery’s API response after doing a query:
https://gist.github.com/ebendutoit/b160b66f3ba4073686d277524d210b90
The schema shows you how the data is structured and the rows indicate, with “f” for field and “v” for value, what values fit into the schema.
Now, isn’t it easier to read and manipulate the JSON when it looks like this?
https://gist.github.com/ebendutoit/07832a24d9cedf436cb394b7bc6ea136
If you agree, then you’re in good hands.
The solution
Here is the node.js code that does this transformation. Feel free to use it, adapt it to your needs and generally make your life simpler and your data happier. The interface to the function is:
convertBQToMySQLResults(schema, rows)
and you pass in the BigQuery results like so:
// apiResponse is the payload you receive from a BigQuery query API // response
convertBQToMySQLResults(apiResponse.schema.fields, apiResponse.rows)
https://gist.github.com/ebendutoit/1717e5eba2f55ab23544153d2ef098a8
A JsFiddle demo
Here is a JsFiddle demo of the code to play with:
[wp-js-fiddle url="https://jsfiddle.net/ebendutoit/4rvgnob2/" style="width:100%; height:400px; border:solid #4173A0 1px;"]
In summary
A lot of transformations for JSON exist out there. Recursive solutions aren’t the easiest to debug but they have the simplest code footprint. Stepping through the code with a debugger is the preferred way of looking at such algorithms in “slow-motion”. This article offers one way of flattening complicated nested JSON originating from Google BigQuery into something you can manipulate and use on your own terms. Try it out! Happy converting!