Handle records and arrays

[ArielSSchwartz]

Can extract_data be changed to support optionally returning structured JSON data instead of a data.frame? This is useful when the query results includes ARRAYs and STRUCTs.

[hadley]

I think it should return a data frame with list columns in that scenario. Could you provide a minimal reprex of such data?

[ArielSSchwartz]

Here is an example query:

SELECT * FROM [bigquery-public-data:samples.github_nested] WHERE repository.owner IN ('hadley') LIMIT 100

Rather than having to flatten repeated fields (ARRAYS), I would like to capture the JSON output and load it into a structured list in R.

[hadley]

Thanks, that's useful. I think it should be trivial to return this data as a list-column.

[hadley]

Even with the query explorer, and this simpler query:

SELECT repository 
FROM [bigquery-public-data.samples.github_nested]
WHERE repository.owner IN ('hadley') LIMIT 100

I had to check "allow large results" and uncheck "flatten" results.

Do you have an example of this working elsewhere? (I just want to double check that it is really this hard; I thought it would be easier)

[ArielSSchwartz]

Yes. This this the only way I know to get nested JSON results from BigQuery Web UI.
Using the command line tools you can try something like:
bq --format prettyjson query "SELECT repository.* FROM [bigquery-public-data.samples.github_nested] WHERE repository.owner IN ('hadley') LIMIT 100"

[craigcitro]

You can also use the UI -- use standard SQL, and click the button to view results as JSON.

The --format flag was, in fact, designed for precisely this use case, so kudos for finding it. 😁

[hadley]

Reprex with latest (in-dev) API:

tb <- bq_project_query(bq_test_project(), 
  "SELECT * FROM publicdata.samples.github_nested WHERE repository.owner IN ('hadley') LIMIT 100",
  use_legacy_sql = FALSE
)
df <- bq_table_download(tb)

It's a bit harder than I had ancticipated thanks to the extreme nesting of the json.

[hadley]

Focussing the query a little to get a smaller selection repeated and non-repeated records.

tb <- bq_project_query(bq_test_project(), 
  "SELECT repository, type, payload.member, payload.shas
    FROM publicdata.samples.github_nested 
    WHERE repository.owner IN ('hadley') 
    LIMIT 100",
  use_legacy_sql = FALSE
)

[zippeurfou]

Here is another example:

query <- "SELECT APPROX_QUANTILES(x, 4) AS output, 2 as t FROM UNNEST(GENERATE_ARRAY(1, 100)) AS x"
mydf <- query_exec(query, project = project, use_legacy_sql = FALSE, max_page = Inf)

> mydf
  output  t
1      1 25
2     50 75
3    100  2

vs what it look like from the UI

and the json

[
  {
    "output": [
      "1",
      "25",
      "50",
      "75",
      "100"
    ],
    "t": "2"
  }
]

Doing the same query the way @hadley did trigger an error:

> tb <- bq_project_query(project,query,use_legacy_sql=FALSE)
Complete
Billed: 0 B
> df <- bq_table_download(tb)
Error in bq_tabledata_to_list(json) :
  embedded nul in string: '\001\0\0\0\001'

[hadley]

Thanks @zippeurfou here's an even more minimal example for testing:

tb1 <- bq_project_query(bq_test_project(), "SELECT STRUCT(1 AS a, 'abc' AS b) as x")
bq_table_download(tb1)

tb2 <- bq_project_query(bq_test_project(), "SELECT GENERATE_ARRAY(1, 10) as x")
bq_table_download(tb2)

[bbhoss]

It seems that the schema generator doesn't support saving lists back to bigquery as a repeated field? Am I missing something or should I open a ticket?

[hadley]

@bbhoss please file a new issue with simple reprex.