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:
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!
