Building key-value pairs from field/value row nests

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:

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?

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)

A JsFiddle demo

Here is a JsFiddle demo of the code to play with:

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!