Generate Nested JSON using BigQuery

I had an interesting use case as part of my work wherein we needed to generate Nested JSON out of table definitions (i.e. from the INFORMATION_SCHEMA.COLUMNS) that was then used by other system for further processing. Any changes being done on the schema, say adding or removal of column or data type change, we were doing this manually to the JSON file. This process was error prone. As the number of tables to maintain increased, the vulnerability of manually modifying the JSON as and when changes being done at the table level happened.

Looking for simpler solution when I looked around google BigQuery had some ready made operators up to the task namely –

  1. ARRAY_AGG – This returns an ARRAY of expression values specified and can also work with aggregation.
  2. STRUCT – Constructs a container of ordered fields i.e. like a list in python. Returns an ARRAY object.

Let me illustrate the use case with an example. Say we have following dataset (data taken from here)-

Marvel and DC Superhero character info


To obtain the JSON file in the following format. There are 2 nested levels that I want to generate. Level 1 with Name, Race, Gender (Under Superhero), Level 2 with Height, Weight, SkinColor, EyeColor (Under Attributes)-


Here is how the query for the solution would look like –

    ,ARRAY_AGG(STRUCT(Height, Weight, SkinColor, EyeColor)) AS Attributes
  FROM `gcp-project-name.dataset_name.marvel_characters_info`
  GROUP BY 1,2,3,4
  ,ARRAY_AGG(STRUCT(Name, Race, Gender, Attributes)) AS Superhero

Output would be as shown below –

BigQuery out of the box provides option to download the data in JSON format and that would result in the format as mentioned in the problem statement. If you are working on automating this say using Python, then you would need to use python libraries ( and call the SQL, then use Pandas dataframe to store the output.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s