TO_JSON_STRING is a BigQuery function that converts a SQL value into a JSON-formatted STRING. You call it as TO_JSON_STRING(value[, pretty_print]), and BigQuery returns the value as JSON text you can store, compare, log or pass to another system. It works on almost any BigQuery type, including STRUCT, a record of named fields, and ARRAY, an ordered list of values. It also handles nested combinations of both, which makes it a common tool for comparing entire rows between two tables in a single QA check (see this LinkedIn post for the row-comparison technique).

TO_JSON_STRING syntax

TO_JSON_STRING(value[, pretty_print])

• value is any BigQuery expression: a column, a STRUCT, an ARRAY, or a combination of these.

• pretty_print is optional and defaults to FALSE. Set it to TRUE to get newlines and indentation in the output instead of a single compact line.

BigQuery JSON to String: Converting with TO_JSON_STRING

The simplest case is a scalar or an array:

SELECT TO_JSON_STRING([1, 2, 3]) AS json_string;

Result:

[1,2,3]

A STRUCT converts to a JSON object, using the struct's field names as keys:

SELECT TO_JSON_STRING(STRUCT('John' AS name, 30 AS age)) AS json_string;

Result:

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

Pretty-printing the output

Pass TRUE as the second argument to format the result for reading instead of storage:

SELECT TO_JSON_STRING(STRUCT('John' AS name, 30 AS age), true) AS json_string;

Result:

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

Converting a whole row, with nested arrays

This is the pattern that makes TO_JSON_STRING useful beyond a single value: turning a row and its related child rows into one JSON string. The query below is self-contained, so you can run it in BigQuery as it is:

WITH orders AS (
  SELECT 1 AS order_id, 'Ana Muller' AS customer_name, 'Keyboard' AS product_name, 2 AS quantity, 45 AS price
  UNION ALL
  SELECT 1, 'Ana Muller', 'Mouse', 1, 25
  UNION ALL
  SELECT 2, 'Jon Voss', 'Monitor', 1, 199
)
SELECT
  TO_JSON_STRING(
    STRUCT(
      order_id,
      customer_name,
      ARRAY_AGG(STRUCT(product_name, quantity, price) ORDER BY product_name) AS line_items
    )
  ) AS order_json
FROM orders
WHERE order_id = 1
GROUP BY order_id, customer_name;

Result:

{"order_id":1,"customer_name":"Ana Muller","line_items":[{"product_name":"Keyboard","quantity":2,"price":45},{"product_name":"Mouse","quantity":1,"price":25}]}

The ORDER BY inside ARRAY_AGG matters. BigQuery does not guarantee the row order inside an aggregated array unless you tell it one, so without ORDER BY the order of line_items could change between runs even though the data hasn't.

Compare two tables row by row with TO_JSON_STRING

Because TO_JSON_STRING turns a whole row into one string, two rows can be compared with a single equality check instead of one condition per column. This query checks a staging table against production after a load:

SELECT
  TO_JSON_STRING(prod) = TO_JSON_STRING(stg) AS is_column_match,
  COUNT(*) AS row_count
FROM (
  SELECT * EXCEPT (loaded_at)
  FROM analytics.metrics_daily
) prod
FULL JOIN (
  SELECT * EXCEPT (loaded_at)
  FROM staging.metrics_daily
) stg
  ON prod.id = stg.id
GROUP BY 1;

A single true row means every one of the 100 rows matched.

Three details make this work:

1. EXCEPT (loaded_at) drops columns that are expected to differ, such as load timestamps.

2. Both sides must have the same columns in the same order, because the JSON strings are compared as text.

3. The FULL JOIN keeps rows that exist on only one side. For those rows one alias is NULL, the comparison is not true, and they show up in the mismatch count.

TO_JSON_STRING vs TO_JSON

Both are functions in BigQuery, not operators. The difference is the return type:

• TO_JSON_STRING returns a STRING. Use it when you need JSON as text, for example to write it to a STRING column, compare it to another string, or export it.

• TO_JSON returns a value of the JSON data type. Use it when you want to keep working with the result as JSON inside BigQuery, since the JSON type supports field access and the JSON functions directly, without parsing a string first.

SELECT
  TO_JSON_STRING(STRUCT(1 AS a, 'x' AS b)) AS as_string,
  TO_JSON(STRUCT(1 AS a, 'x' AS b)) AS as_json;

Both columns display the same text, {"a":1,"b":"x"}, but as_string is a STRING column and as_json is a JSON column. Only as_json supports a path expression like as_json.a without an extra parsing step.

BigQuery String to JSON: Converting with PARSE_JSON

Going the other way, from a JSON-formatted string to a usable value, uses PARSE_JSON and JSON_VALUE instead of TO_JSON_STRING.

PARSE_JSON: string to the JSON type

SELECT PARSE_JSON('{"name":"John","age":30}') AS json_value;

Result: a value of the JSON type rather than a STRING. Once you have a JSON value, you can read a field with a path expression:

SELECT PARSE_JSON('{"name":"John","age":30}').name AS name_value;

Result: "John" (the field access on a JSON value still returns JSON, so the string comes back quoted; wrap it in JSON_VALUE if you want a plain STRING instead).

JSON_VALUE: pull one scalar out as a STRING

SELECT JSON_VALUE('{"name":"John","age":30}', '$.name') AS name_value;

Result: John, an unquoted SQL STRING. JSON_VALUE works on both a JSON-formatted STRING and a JSON-typed column, and it returns NULL if the path points at an object or an array instead of a scalar. It is the function to use when you want a plain string back, since it strips the quotes and unescapes the value for you.

Examples of TO_JSON_STRING, recap

1. TO_JSON_STRING([1, 2, 3]) for a plain array.

2. TO_JSON_STRING(STRUCT('John' AS name, 30 AS age)) for a single row as a flat object.

3. TO_JSON_STRING(STRUCT(order_id, customer_name, ARRAY_AGG(STRUCT(...)) AS line_items)) for a row with related child rows nested inside it.

TO_JSON_STRING is a useful tool for QA row comparisons, for logging a row's full state at a point in a pipeline, and for producing JSON to hand to a system outside BigQuery.

FAQs

What is the difference between TO_JSON and TO_JSON_STRING in BigQuery?

TO_JSON_STRING returns a STRING containing JSON text. TO_JSON returns a value of the JSON data type. Both are functions. Use TO_JSON_STRING when you need JSON as text; use TO_JSON when you want to keep querying the result as JSON inside BigQuery.

How do I convert a string to JSON in BigQuery?

Use PARSE_JSON to turn a JSON-formatted STRING into a value of the JSON type. Use JSON_VALUE if you just want one scalar field out of that JSON as a plain STRING, rather than the whole JSON value.

How do I pretty-print JSON output in BigQuery?

Pass TRUE as the second argument to TO_JSON_STRING: TO_JSON_STRING(value, true). BigQuery formats the result with newlines and indentation instead of one compact line.

Does TO_JSON_STRING work on an entire row, not just one value?

Yes. Wrap the columns you want in a STRUCT and pass that STRUCT to TO_JSON_STRING. For related child rows, aggregate them into an array first with ARRAY_AGG and STRUCT, then nest that array inside the outer STRUCT, as shown in the orders example above.

Sources

• JSON functions - Google Cloud BigQuery documentation