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