The TO_JSON_STRING Function: Everything You Need to Know

BigQuery, a managed data warehouse from Google Cloud, is full of powerful functions. Some of them are super known and commonly used, and others are not so famous. In this article, we are going to dig into the not so known function of TO_JSON_STRING. The main purpose of this function is to be used as a QA tool. You can perform a row-by-row comparison of data between two tables by transforming all columns in a table into a single JSON-formatted string. This enables you to compare entire rows efficiently ( see LinkedIn post here).

What does TO_JSON_STRING do?

TO_JSON_STRING function is a bulit-in function in BigQuery. Its purpose is to convert data to JSON format. This process involves transforming structured data into a text-based representation following the JSON (JavaScript Object Notation) standard. As a result, it allows data to be easily transmitted, exchanged, and interpreted by different systems, making it a popular choice for data interchange in web applications and APIs.

How does TO_JSON_STRING work?

The TO_JSON_STRING function accepts various data types, such as strings, numbers, dates, and arrays, and produces a JSON string as its result. This JSON string represents the input data in a format that can be readily interpreted by other applications and systems**.**

What is TO_JSON_STRING function syntax?

TO_JSON_STRING(value[, pretty_print])

The parameter pretty_print is an optional one. If this parameter is set to true, the function will return a formatted JSON string that is easier to read.

What is the difference between TO_JSON_STRING VS TO_JSON ?

Both of these functions are used to convert data into JSON format, however there are some differences between these two.

• TO_JSON_STRING: this function returns a JSON-formatted string. It is frequently used for converting individual values to JSON strings

• TO_JSON: is an operator and not a function. The purpose of this operator is to case the whole row into a JSON object. It is frequently used when you want to convert entire rows or query results to JSON objects.

Examples of TO_JSON_STRING function

1. In this first example we convert a Struct function to JSON string

SELECT TO_JSON_STRING(STRUCT<name STRING, age INT64>('John', 30)) AS json_string

The outcome would be:

{"name":"John","age":30}

2. A bit more complicated example is following. Let’s assume we have an orders table:

We want to show the data for order_id = 1 in JSON format. In order to do so, we need to used the function TO_JSON_STRING.

SELECT TO_JSON_STRING(STRUCT(
  order_id,
  customer_name,
  ARRAY_AGG(STRUCT(product_name, quantity, price)) AS products
))AS json_data
FROM orders
WHERE true
and order_id = 1
GROUP BY order_id, customer_name;

The result of the aforementioned query will be presented in JSON format like so:

Overall, the TO_JSON_STRING function is a very valuable function for converting data structures into a standardized JSON format. The TO_JSON_STRING function is a valuable tool for data scientists, engineers, and other professionals who need to work with JSON data.