JSON vs Structs vs Columns in BigQuery - Observations

Shout out to Josh Peng and Claus Herther who inspired me to write this post! A common way of logging and delivering data from production systems is via the JSON format. JSON allows for a flexible schema that supports nested value pairs and arrays. In BigQuery we have a few options to consider when choosing how to store this data for use in BigQuery. JSON string column with BigQuery JSON functions Pros:  Easiest to use directly from the source system Flexible schema as source data changes Cons: We lose BigQuery’s columnar data storage benefits. Every query will pay the cost of hitting the entire JSON object. JSON functions are somewhat limited Transform the JSON into a Structs Pros: Depending on your use case this may better fit your data needs, especially if you have nested arrays in your JSON Efficient use of columnar data storage Cons: Lack of schema flexibility May be new territory for SQL users who are not used to using nested fields Transform the JSON into a standard table with


This should be fun! This site started out as a bit of a joke with a few data professionals. With a snarky redirect to an Amazon listing for SQL All-in-One For Dummies (For Dummies (Computer/Tech))

Upon further reflection, it became clear that there may actually be an opportunity to capture useful data knowledge and perhaps point people in the right direction. Alas, Letmesqlthatforyou.com was born, also available in the shorter url lmsqltfy.com if you prefer.

More to come soon! The plan is to blog about SQL, BigQuery, Snowflake, Apache Spark, Python, Scala, Jupyter, Hadoop, Hive, Presto, and so much more.


  1. Don’t let confusion over a word or phrase trigger you to make a foul transfer. Being a soldier in ancient Rome was not a very enjoyable and perspective occupation. Aside from the quick life expectancy, additionally they needed to cope with witnessing their friends and comrades being continually wounded and murdered in battle. This was more than sufficient to deliver the soldiers’ morale down, thus lowering their effectiveness on the battlefield. A line wager consists of two road bets may be} marked by a counter. Along the lengthy line you mark in the t-cross and canopy six numbers 빅카지노 e.g. four, 5, 6, 7, 8 and 9.


Post a Comment

Popular posts from this blog

JSON vs Structs vs Columns in BigQuery - Observations

Google BigQuery and Colab - Getting Started