Data type mappings in BigQuery

This page describes data type mappings from various source databases to their corresponding BigQuery data types. Understand how different data types convert when migrating data to BigQuery, how BigQuery represents MongoDB binary JSON documents, and how to query PostgreSQL array data as a BigQuery ARRAY data type.

Map data types

The following table lists data type conversions from supported source databases to the BigQuery destination.

MongoDB data types

MongoDB binary JSON (BSON) documents are written to BigQuery in MongoDB Extended JSON (v1) strict mode format. The table shows how data types are represented in BigQuery, along with example values.

[
    "item1",
    123,
    true,
    { subItem: "object in array" }
  ]

Query a PostgreSQL array as a BigQuery array data type

If you prefer to query a PostgreSQL array as a BigQuery ARRAY data type , you can convert the JSON values to a BigQuery array using the BigQuery JSON_VALUE_ARRAY function:

  
 SELECT 
  
 ARRAY 
 ( 
 SELECT 
  
 CAST 
 ( 
 element 
  
 AS 
  
  TYPE 
 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 JSON_VALUE_ARRAY 
 ( 
  BQ_COLUMN_NAME 
 
 , 
 '$' 
 )) 
  
 AS 
  
 element 
 ) 
  
  
 AS 
  
 array_col 
  

Replace the following:

• TYPE : the BigQuery type that matches the element type in the PostgreSQL source array. For example, if the source type is an array of BIGINT values, then replace TYPE with INT64 .

  For more information about how to map the data types, see Map data types .

• BQ_COLUMN_NAME : the name of the relevant column in the BigQuery table.

There are 2 exceptions to the way that you convert the values:

• For arrays of BIT , BIT_VARYING or BYTEA values in the source column, run the following query:

   SELECT 
    
   ARRAY 
   ( 
   SELECT 
    
   FROM_BASE64 
   ( 
   element 
   ) 
    
   FROM 
    
   UNNEST 
   ( 
   JSON_VALUE_ARRAY 
   ( 
    BQ_COLUMN_NAME 
   
   , 
   '$' 
   )) 
    
   AS 
    
   element 
   ) 
    
    
   AS 
    
   array_of_bytes 
  

• For arrays of JSON or JSONB values in the source column, use the JSON_QUERY_ARRAY function:

   SELECT 
    
   ARRAY 
   ( 
   SELECT 
    
   element 
    
   FROM 
    
   UNNEST 
   ( 
   JSON_QUERY_ARRAY 
   ( 
    BQ_COLUMN_NAME 
   
   , 
   '$' 
   )) 
    
   AS 
    
   element 
   ) 
    
    
   AS 
    
   array_of_jsons