2

I'm following the example given in https://cloud.google.com/bigquery/create-simple-app-api#bigquery-simple-app-java to obtain query results from BigQuery API.

TableResult result = queryJob.getQueryResults();

It returns the results in a TableResults type but I need to get the results in a json format.

TableResult{rows=[[FieldValue{attribute=PRIMITIVE, value=(...)},
    FieldValue{attribute=PRIMITIVE, value=(...)},
    FieldValue{attribute=PRIMITIVE, value=(...)},
    FieldValue{attribute=PRIMITIVE, value=(...)},
    FieldValue{attribute=PRIMITIVE, value=(...)},
    FieldValue{attribute=PRIMITIVE, value=(...)},
    FieldValue{attribute=PRIMITIVE, value=(...)}],(...)

How can I transform the results in TableResults type to json, or even csv?

pirho
  • 11,565
  • 12
  • 43
  • 70
Filipa Ivars Silva
  • 23
  • 1
  • 3

1 Answers1

2

To convert rows in a table to JSON you can use the function TO_JSON_STRING [1]. To get a JSON with formatting you need to pass the parameter “true” to the function.

The new query would look like this:

#standardSQL
“SELECT TO_JSON_STRING(t,true)"
 + "FROM ( 
    SELECT CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
     + "view_count "
     + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
     + "WHERE tags like '%google-bigquery%' "
     + "ORDER BY favorite_count DESC LIMIT 10) as t”

Output:

TableResult{rows=[[FieldValue{attribute=PRIMITIVE, value={
  "url": "https://stackoverflow.com/questions/6607552",
  "view_count": 24524
}}], [FieldValue{attribute=PRIMITIVE, value={
  "url": "https://stackoverflow.com/questions/20349189",
  "view_count": 4298
}}], [FieldValue{attribute=PRIMITIVE, value={
  "url": "https://stackoverflow.com/questions/22734777",
  "view_count": 7940
}}], [FieldValue{attribute=PRIMITIVE, value={
  "url": "https://stackoverflow.com/questions/27537720",
  "view_count": 2039
(...)
gr7
  • 492
  • 3
  • 11