Is there an easy way to convert rows in BigQuery to JSON?

Question

I am trying to reduce my rows in my BigQuery Table to JSON string. Is there a quick way to do this in standard SQL?

I thought about calling js with User-defined Function, but it forces me to declare the input type of the UDF so I can't generalize this function.

Answer 1

This is now possible using TO_JSON_STRING, which can also help when you want to pass a row from your table to a JavaScript UDF.

#standardSQL
WITH MyTable AS (
  SELECT 1 AS x, 'foo' AS y, true AS z UNION ALL
  SELECT 2, 'bar', false
)
SELECT TO_JSON_STRING(t) AS json
FROM MyTable AS t;

Answer 2

If you want to glue together all of the rows quickly into a JSON block, you can do something like:

SELECT CONCAT("[", STRING_AGG(TO_JSON_STRING(t), ","), "]")
FROM `project.dataset.table` t

This will produce a table with 1 row that contains a complete JSON blob summarizing the entire table.