BigQuery wrapper: JSON, Numeric not supported

[yan-hic]

Trying to query a table with JSON data type in BigQuery.

Foreign table, with json column type, gets created fine but upon querying, the wrapper returns:

Failed to run sql query: get table metadata failed: Request error (error: error decoding response body: unknown variant `JSON`, expected one of `STRING`, `BYTES`, `INTEGER`, `INT64`, `FLOAT`, `FLOAT64`, `NUMERIC`, `BIGNUMERIC`, `BOOLEAN`, `BOOL`, `TIMESTAMP`, `DATE`, `TIME`, `DATETIME`, `RECORD`, `STRUCT` at line 15 column 22)

[yan-hic]

Workaround:

1. cast to string on BQ side with to_json_string()
2. create PG view that casts back to json
3. query that column in graphQL

Interestingly, a view is not necessary if using SQL i.e. found out that, using the foreign table definition as-is (defining column type as json), select column->'sub' from table works.
However

 {
      tableCollection {
        edges {
          node{
            column{
              sub
            } 
	  }
	}
 }

does not work, and returns full column as text value.

EDIT

Documentation was just updated and JSON is always serialized as string in GraphQL: https://supabase.github.io/pg_graphql/api/#json. In SQL or API, one would just cast like json-string::json until the FDW supports it natively.

[burmecia]

Yes, JSON is not support yet in BigQuery FDW. A workaround is using TO_JSON_STRING to convert JSON to STRING in BigQuery and store that string as source table column. Then use subquery or view on top of PG foreign table and convert that string back to JSON.

[yan-hic]

@burmecia thanks - do you have an ETA and would the wrapper be available in the cloud immediately when released ? I have a few tables with JSON data types in BigQuery and am not authorized to create views (dataset owned by my client).

Ideally, table option would take a subquery as mentioned here. The benefit of it is: subset of columns and computed fields pushed at the source (like casting...). I can create a separate FeatReq for it.

[burmecia]

We're currently working on improving BigQuery fdw, the JSON support should be ready on the next release. And thanks for the subquery in table option, it is quite useful, we will evaluate and hopefully include it in the future release.

[yan-hic]

EDIT: I have edited the title and added Numeric:
Failed to run sql query: field type Numeric not supported

which is a valid type in PG: https://www.postgresql.org/docs/current/datatype-numeric.html and in BigQuery (and is supported by https://github.com/lquerel/gcp-bigquery-client already)

Would be good to add all supported ones or document the limited support.
Thanks.