How to preserve integer data type when exporting to JSON?

Question

When I export my bigquery tables in JSON format, the INTEGER fields are converted to strings. Is there any way to maintain the integer data type when exporting?

Here are the minimum steps to reproduce the integer->string conversion phenomenon:

1. Run the query SELECT INTEGER(1) AS myInt and save the result to a table. Note that the output table schema shows the type as INTEGER.
2. Export the table in JSON format. The output will be: {"myInt":"1"}

In the JSON format, "1" is a string, not an integer.

Answer 1

This is not currently possible; the reasoning is because of an unfortunate combination of the Javascript spec, IEEE floating point precision, JSON, and BigQuery integer sizes.

In Javascript, all numbers must be representable as IEEE754 double-precision floating point values. Javascript parses JSON numbers into javascript numbers. BigQuery uses 64-bit signed integer values.

The problem comes because not all 64-bit integer values can be represented as IEEE 754 double precision floating point values. (it is easy to see why: IEEE 754 double-precision floating point uses 64 bits but can represent lots of things that aren't integers; therefore, there must be 64 bit integers that it can't represent).

So in order to make BigQuery JSON responses work in Javascript, integer values are wrapped in quotes, so that no precision is lost.

That said ... the decision to represent integers as strings in API request makes sense, since many of the callers of the API will be in javascript. There doesn't seem to be as compelling of an argument to not represent integers as numbers when exporting data. (other than to change it now would be a breaking change).

Can you file a bug at the BigQuery issue tracker to fix this? (it would likely involve another flag in the export configuration).