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.
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...
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 ...
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...
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...
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...
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...
