Cloud Intelligence™
Transforming BigQuery JSON API responses recursively
This page is also available in Deutsch, Español, Français, Italiano, 日本語, and Português.
About Eben Du Toit
I lead engineering teams in the Partner Channel space, building the tooling that helps customers make sense of their revenue — turning complex partner and billing data into something they can actually see, trust, and act on. The work sits at the intersection of cloud cost management, FinOps, and billing integrations, with data flowing across GCP, BigQuery, MongoDB Atlas, Snowflake, and the wider cloud ecosystem. It's technically meaty, and I love that about it.
My days are split between people and systems — growing and aligning multiple teams, helping the team navigate the environment, hiring, and partnering with stakeholders across the org. I shift between maker mode and manager mode depending on what's needed: sometimes I'm deep in the code building features, sometimes I'm in the room helping my teams move forward. I care a lot about building teams where good engineers can do their best work.
What I work on
Partner Channel engineering and revenue management tooling · Cloud cost management and FinOps · Billing data pipelines and integrations · Engineering leadership, hiring, and team process
Good topics to find me for
Anything Partner Channel, revenue, FinOps, or billing-related · Data engineering and pipeline architecture · Engineering management, hiring, or team culture
Outside work
When I'm not in a terminal, I'm usually behind a camera — shooting on a Nikon Z or whatever vintage film body I'm currently infatuated with. I follow mountain biking and trail running closely, have strong opinions about mechanical keyboards and good stationery, and watch more anime than I probably admit. I also play Rocket League with more optimism than results.
Cape Town · SAST (UTC+2)
My personal page
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 // responseconvertBQToMySQLResults(apiResponse.schema.fields, apiResponse.rows)https://gist.github.com/ebendutoit/1717e5eba2f55ab23544153d2ef098a8
Cloud bill shouldn't be a mystery
One platform for AI and Cloud optimization.
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!
