JSON_EXTRACT in BigQuery – Extract data from JSON

I already wrote several articles on BigQuery because I love data analytics. People who are working with Big Data, they are already familiar with BigQuery. I have more than 100 active datasets with billions of records in it. I play with them frequently. Today I am writing about to extract data from JSON by using JSON_EXTRACT in BigQuery.

Table of Contents in this article

What is BigQuery?

BigQuery is a serverless platform as a service where you can analysis massive amount of data for meaningful insights through RESTful web service. In 2010 Google launched BigQuery and they already improved it much further and it’s capable to deal with billion, trillion data.

What is JSON?

JavaScript Object Notation (JSON) is an open-standard file format that uses human-readable text to transmit data objects consisting of attribute–value pairs and array data types. It’s one of the most usable format worldwide and programmers love this.

Extract data using JSON_EXTRACT in BigQuery

If you have lots of logs or data in BigQuery that are in JSON format, you need to traverse the JSON key value pair and need to extract data or need to access key value for other BigQuery operation. You should use JSON_EXTRACT() or JSON_EXTRACT_SCALAR() function. Here is a simple query to extract data by using JSON_EXTRACT() in BigQuery.

SELECT JSON_EXTRACT('{"job_slugs":"I am not a monster"}', '$.job_slugs') as job_slugs from table

It can work based on JSON path. You can learn and evaluate your JSON path from http://jsonpath.com. It’s awesome

Learn more BigQuery tips & tricks from here.

Shaharia is a professional software engineer with more than 10 years of experience in the relevant fields. Digital ad certified, cloud platform architect, Big data enthusiasts, tech early adopters.

Site Footer