What I run:

I run this in the BigQuery console:

SELECT CAST([1, 2, 3] AS ARRAY<INTEGER>) AS col

I select to view the results as JSON and get:

[{
  "col": ["1", "2", "3"]
}]

What's wrong:

The values in the list have been cast to STRING.

What I expect:

The values in the list should be numeric (unquoted).

What I run:

I run this in the BigQuery console:

SELECT CAST([1, 2, 3] AS ARRAY<INTEGER>) AS col

I select to view the results as JSON and get:

[{
  "col": ["1", "2", "3"]
}]

What's wrong:

The values in the list have been cast to STRING.

What I expect:

The values in the list should be numeric (unquoted).

• google-bigquery

1 Answer 1

These are JSON numbers in string format. Based on SQL syntax notation rules. The GoogleSQL documentation commonly uses the following syntax notation rules and one of those rules are: Double quotes ": Syntax wrapped in double quotes ("") is required.

Based on working with JSON data in GoogleSQL, By using the JSON data type, you can load semi-structured JSON into BigQuery without providing a schema for the JSON data upfront. This lets you store and query data that doesn't always adhere to fixed schemas and data types. By ingesting JSON data as a JSON data type, BigQuery can encode and process each JSON field individually. You can then query the values of fields and array elements within the JSON data by using the field access operator, which makes JSON queries intuitive and cost efficient.