BigQuery has a lot of options and capabilities, with its handful of built-in methods at our disposal. In this post, I’d like to show you how we may take advantage on JSON parsing and JavaScript inside queries!

First of all, lets define some data, shall we? The table name is my_table. Let's have a look at it:

This is a really simple data structure, but it resembles something that I've experienced in the past. This log came from a Ruby application, and if you know Ruby, you’ll probably notice that params inside payload is a Ruby Hash. Not ideal, but it happens.

Queries with JSON

Now, our first task is to simply show all deletion logs. Easy right?

SELECT id, payload

FROM my_table

WHERE payload LIKE '%delete%'

And it works! Well... kinda. If any param had delete anywhere else, we would’ve fetched some wrong rows. We must make sure to look inside the action key only. Let’s do this:

SELECT id, payload

FROM my_table

WHERE JSON_EXTRACT_SCALAR(payload, '$.action') = 'delete'

Now we are talking! BigQuery has a built-in JSON parse for us to use and abuse. Let’s say we want to return only the params of each create action, like this:

SELECT id, JSON_EXTRACT_SCALAR(payload, '$.params') AS params

FROM my_table

WHERE JSON_EXTRACT_SCALAR(payload, '$.action') = 'create'

and it works perfectly.

Using JavaScript inside queries

Now let’s get to the more advanced stuff. The first one is: fetch the params of every create action, but in JSON format instead of Ruby's hash.

For this problem, we must parse the params, and BigQuery has no pre-built methods for that. So, let's create our own!