Correctly export numeric lists as JSON in BigQuery - Stack Overflow

admin管理员组

文章数量:1355068

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

Share Improve this question Follow asked Mar 28 at 12:51 Ben WatsonBen Watson 5,55166 gold badges4343 silver badges7171 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： Correctly export numeric lists as JSON in BigQueryStack Overflow