Unknown type JSON

[Kvit]

Getting error "Unknown type JSON" from function bq_table_download() while trying to download table that includes field of type JSON .

to reproduce, in Google Cloud SQL Workspace

CREATE TABLE bigquery (
  id INT,
  data JSON
);

INSERT INTO bigquery (id, data)
VALUES (1, '{"name": "John", "age": 30, "city": "New York"}');

bq_table_download()

[Kvit]

I can get around the issue by converting JSON to plain string in the new table using the conversion function TO_JSON_STRING(json), then dropping the column, then converting string to JSON again, but it is a lot of steps.

[hadley]

In R, would expect to get a parsed object or just a string containing JSON?

[Kvit]

the object would be ideal but the sting will do too, perhaps it can be an option parse_json = c("string", "object") ?

[hadley]

Could you help me get your SQL to work? This is what I see:

library(bigrquery)
options(gargle_oauth_email = TRUE)

con <- DBI::dbConnect(bq_test_dataset())
#> ℹ The bigrquery package is using a cached token for 'hadley@posit.co'.

DBI::dbExecute(con, "CREATE TABLE bigquery (
  id INT,
  data JSON
);")
#> [1] 0
DBI::dbExecute(con, r"(INSERT INTO bigquery (id, data)
VALUES (1, '{"name": "John", "age": 30, "city": "New York"}');)")
#> Error in `bq_job_wait()` at bigrquery/R/dbi-result.R:21:3:
#> ! Job 'gargle-169921.job_56LyAlmx4LgUkfwQ_97LO2Q2LaAd.US' failed
#> ✖ Value has type STRING which cannot be inserted into column data, which has type JSON at [2:12] [invalidQuery]

^{Created on 2023-11-09 with reprex v2.0.2.9000}

[hadley]

Don't worry, found an example in the google docs:

library(bigrquery)
options(gargle_oauth_email = TRUE)

ds <- bq_test_dataset()
#> ℹ The bigrquery package is using a cached token for 'hadley@posit.co'.
con <- DBI::dbConnect(ds)

DBI::dbExecute(con, "CREATE TABLE table1(
  id INT64,
  cart JSON
)")
#> [1] 0
DBI::dbExecute(con, r"(
  INSERT INTO table1 VALUES
  (1, JSON '{"name": "Alice", "age": 30}'),
  (2, JSON_ARRAY(10, ['foo', 'bar'], [20, 30])),
  (3, JSON_OBJECT('foo', 10, 'bar', ['a', 'b']));
)")
#> [1] 3
DBI::dbReadTable(con, "table1")
#> Error in eval(expr, envir, enclos): Unknown type JSON

^{Created on 2023-11-09 with reprex v2.0.2.9000}

[hadley]

Hmmm, I think I'm going to move to a strategy where for unknown field types I just return the string that BigQuery sends me. Then it'll be up to you to parse it.