Search for a command to run...
Series
Working with JSON in BigQuery — the native JSON datatype, extracting and transforming JSON data, LAX conversion functions, and handling semi-structured payloads.
The JSON datatype in BigQuery. This topic has been sitting in my Notion list of post ideas for some time. So in our beloved BQ it is a native, standalone data type, not just another STRING 😁, although strings can of course hold json-like strings. As...
In one my previous posts, we've briefly introduced the JSON datatype in BigQuery. But did anyone notice how most of the JSON functions have signatures for both a JSON-type input and a json-formatted string input? What is the difference between the tw...
Here's a mildly interesting function if you're working with JSON in BigQuery. JSON_TYPE takes in a JSON value and returns the name of the respective JSON type (object, array, string, number, boolean, null) as a STRING. See below an illustration of it...
So if you're looking to decompress after a long week and relax, check out the LAX conversion functions for handling JSON conversions in BigQuery. There are 4 separate functions: LAX_STRING, LAX_BOOL, LAX_FLOAT64, LAX_INT64 - with each one of them att...
I was asked today about dynamically extracting key-value pairs from heterogeneous JSON-like strings in BigQuery SQL and I've remembered this interesting approach I've seen a while ago. It leverages regular expressions, one of everyone's favorites, I ...
A couple of months ago, I've posted about dynamically extracting key-value pairs from JSON in BigQuery SQL which leveraged regex (check comments). Shortly after that post, we've gotten a new built-in function to dynamically extract the keys occurring...
