CODEWITHSUNDEEP
jsongoogle-bigquery
Andrew Cassidy
Andrew Cassidy

Reputation: 2998

BigQuery downloading or exporting as JSON does not respect types

Query I run in BigQuery:

SELECT 5 as a

Schema I get when I run the above query and select Save as Table:

enter image description here

JSON I get when I download the query result as JSON or when I export the table:

{"a":"5"}

Shouldn't this be: 

{"a":5}

Upvotes: 0

Views: 704

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Consider using the TO_JSON_STRING function. From the documentation, the representation of INT64 is:

Same as CAST(value AS STRING) when value is in the range of [-253, 253], which is the range of integers that can be represented losslessly as IEEE 754 double-precision floating point numbers. Values outside of this range are represented as quoted strings.[

To return query results using this function, you can use this form:

SELECT TO_JSON_STRING(t)
FROM (
  <your query here>
) AS t

Upvotes: 2

chuckx
chuckx

Reputation: 6877

From Exporting Table Data - Export Limitations:

When you export data in JSON format, INT64 (integer) data types are encoded as JSON strings to preserve 64-bit precision when the data is read by other systems.

Upvotes: 4

Related Questions