Amazing New Feature

Google BigQuery now supports JSON as a Data Type

How to use JSON as a Data Type in BigQuery SQL

As of today, Google announced that BigQuery now provides the JSON data type for storing and manipulating data. This is of course a super handy feature since JSON is a frequently used data format. By using the JSON data type, you can ingest semi-structured JSON into BigQuery without providing a schema for the JSON data upfront. This lets you store and query data that doesn’t always adhere to fixed schemas and data types [1]. This now brings BigQuery even more into the hybrid world between SQL and NoSQL database.

Here is a small cheat sheet so that you can easily find your way into the new data type.

Create a Table with a JSON Column

Creating a table with a JSON data type is usually done as shown below:

CREATE TABLE yourdataset.table1(

id INT64,

person JSON

);

Insert JSON Values

If you are familiar with JSON, inserting values is also relatively easy.

INSERT INTO yourdataset.table1

VALUES(1, JSON ‘{“name”: “Cooper”, “forname”: Alice}’);

Convert Strings into JSON

This example converts data stored as a string into a JSON type.

SAFE.PARSE_JSON(String)

Extract values as JSON

Querying data via SQL is also as usual. There are two possibilities here. You whether can access the fields expression by using the field access operator:

SELECT person.name FROM yourdataset.table1

Or you use the JSON subscript operator to reference the members of a JSON object by name:

SELECT cart['name'] FROM yourdataset.table1

You should now be able to get a good start in this topic. I personally find the new feature super cool and it makes BigQuery a bit more powerful again. If you want to learn more about the new JSON datatype, you can use the source below. An important note: The whole thing is in the preview and therefore possibly not for everyone already usable. I think, however, as usual from Google, the feature will soon be available everywhere.

Otherwise, have fun trying it out — Cheers!

Sources and Further Readings

[1] Google, Working with JSON data in Standard SQL (2022)