CODEWITHSUNDEEP
google-bigquery
dorachan2010
dorachan2010

Reputation: 1091

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

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.

Upvotes: 17

Views: 26597

Answers (2)

bamnet
bamnet

Reputation: 2554

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.

Upvotes: 7

Elliott Brossard
Elliott Brossard

Reputation: 33765

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;

Upvotes: 23

Related Questions