将BigQuery的具有null的行映射到struct后发生异常

Question

I have a BigQuery table with this schema:

name    STRING  NULLABLE     
age     INTEGER NULLABLE  
amount  INTEGER NULLABLE

and this two rows:

1   Batgirl 23  123  
2   Batman  22  null     

What I am trying to do is a select from Go on this table which is working really fine:

ctx := context.Background()
client, err := bigquery.NewClient(ctx, projectID)

q := client.Query("SELECT * FROM test.user_test LIMIT 1000")

it, err := q.Read(ctx)
if err != nil {
    log.Fatal(err)
}
for {
    var values []bigquery.Value
    err := it.Next(&values)
    if err == iterator.Done {
        break
    }
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(values)
}

the above code is working like a charm, it gets the select and it prints both rows like this:

[Batman 22 <nil>]
[Batgirl 23 123]

Batman is shown with the nil value. The problem comes when I am trying to store this rows in a struct with this code:

type test struct {
    Name   string
    Age    int
    Amount int `nullable`
}

q := client.Query("SELECT * FROM test.test_user LIMIT 1000")

if err != nil {
    log.Fatal(err)
}

it, err := q.Read(ctx)
if err != nil {
    log.Fatal(err)
}
for {
    var c test
    err := it.Next(&c)
    if err == iterator.Done {
        break
    }
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(c)
}

the above code iterates the results query and it stores both values in a struct that I can manipulate later. It works fine when there aren't any null columns (which is not my case now because I have a null amount on Batman), so I am getting the next error:

bigquery: NULL values cannot be read into structs

What I have tried to fix this is making the struct field nullable like this:

type test struct {
    Name   string
    Age    int
    Amount int `bigquery:",nullable"`
}

But this is doing basically nothing. I am starting to learn Go and I don't really understand why I can't have nil values in structs or how I can fix this, so these are my two questions:

1. How can I store my row results with null values?

2. Why has google decided that a nil value can't be set on a struct field?

Answer 1

You can use any of the bigquery.Null types that you can see in this link:

STRING      NullString
BOOL        NullBool
INTEGER     NullInt64
FLOAT       NullFloat64
TIMESTAMP   NullTimestamp
DATE        NullDate
TIME        NullTime
DATETIME    NullDateTime

In your case you would have to change your line

Amount int `nullable`

to:

Amount bigquery.NullInt64

In the same link it states:

It is an error to attempt to read a BigQuery NULL value into a struct field, unless the field is of type []byte or is one of the special Null types: NullInt64, NullFloat64, NullBool, NullString, NullTimestamp, NullDate, NullTime or NullDateTime.