BigQuery multiple data type in one data field

1
votes

Is it possible to define a table schema with field that has multiple data types on it? For example:

BIGQUERY TABLE SCHEMA

schema: 
  [{
    name: 'field1',
    type: 'string',
  },
  {
    name: 'field2',
    type: 'string or bool',
  }]

Can I have a field like field2 in the example above which can have 2 datatype "String or Boolean".

1
javascriptnode.jsgoogle-bigqueryschema
no, that's not possible – Felipe Hoffa
@FelipeHoffa I saw many supported search db like bigquery, elasticsearch has only 1 type in 1 field, so we can not have multiple optional type in 1 field right? – Quoc Van Tang

1 Answers

2
votes

You can have a struct in a field and define several data types there.

#standardsql
CREATE TABLE `test.table1`
(
  column1 STRUCT<
    number INT64,
    text STRING,
    truth BOOL
  >,
  column2 STRING
)

Technically that is one column with several data types - but in different "sub-columns": table schema