Introducing new SQL functions to manipulate your JSON data in BigQuery

Enterprises are generating data at an exponential rate, spanning traditional structured transactional data, semi-structured like JSON and unstructured data like images and audio. Beyond the scale of the data, these divergent types present processing challenges for developers, at times requiring separate processing flows for each. With its initial release BigQuery’s support for semi-structured JSON eliminated the need for such complex preprocessing and providing schema flexibility, intuitive querying and the scalability benefits afforded to structured data. 

Today, we are excited to announce the release of new SQL functions for BigQuery JSON, extending the power and flexibility of our core JSON support. These functions make it even easier to extract and construct JSON data and perform complex data analysis.

With these new query functions, you can:

• Convert JSON values into primitive types (INT64, FLOAT64, BOOL and STRING) in an easier and more flexible way with the new JSON LAX functions

• Easily update and modify an existing JSON value in BigQuery  with the new JSON  mutator functions.

• Construct JSON object and JSON array with SQL in BigQuery with the new JSON constructor functions.

Let's review these new features and some examples of how to use them. First, we will create a table for demonstration.

Great! Let’s say we want to get a list of all users over 30. Looking at the table, you will see that user.age contains a JSON number in the first record, a JSON string in the second, and a JSON null in the third. With the new powerful LAX function, LAX_INT64, all types are automatically inferred and processed correctly.

Unlike the “strict” conversion functions, which require that the JSON type matches the primitive type exactly, the “lax” conversion functions will also handle conversions between mismatched data types. 

For example, the strict conversion function below would return an error:

However, the lax conversion function below would return the desired result:

Furthermore, you can quickly and easily remove a field in the JSON data by using JSON_REMOVE functions.

After running the above function the table is updated with the following contents:

JSON_STRIP_NULLS compresses the data by removing JSON nulls. Although BigQuery null values neither impact performance nor storage cost, it can be helpful for reducing data size during exports.

After running the above function the table is updated with the following contents:

Now, what if we want to modify or add a field to the JSON data? 

You can now update the data with the new JSON_SET function. And you have the ability to mix and match JSON functions together to achieve desired results. 

For example, the query below adds a new field, "region_code", to the table. The value of the field will be "America" if the value of the "country" field is "USA", and "Other" if it is not.

After running the above function the table is updated with the following contents:

Last but not least, let’s say you have a table of property/value pairs you want to convert to a JSON object. With the new JSON_OBJECT constructor function, you can effortlessly create the new JSON object.

Complete list of functions

Lax conversion functions:

• LAX_BOOL

• LAX_INT64

• LAX_FLOAT64

• LAX_STRING

JSON constructor functions:

• JSON_ARRAY

• JSON_OBJECT

JSON mutator functions:

• JSON_ARRAY_APPEND

• JSON_ARRAY_INSERT

• JSON_REMOVE

• JSON_SET

• JSON_STRIP_NULLS

Try it out!

Google BigQuery is constantly adding new features to make it easier and more powerful to analyze your data. We encourage you to check them out and provide your feedback to us as we continue to develop additional features and capabilities to make working JSON easier and faster over time.