Imagine you have a struct with some fields which are always there, but you also have some fields which are dynamically populated with different [key, value] pairs, which cannot be described with static types upfront.

Example Record

1
2
3
4
5
6
7

type Order struct {
    ID         string                 `json:"id"`
	ItemID     string                 `json:"item_id"`
    Category   string                 `json:"category"`
    Timestamp  int64                  `json:"timestamp"`
    Attributes map[string]interface{} `json:"attributes"`
}

The attributes field is map and may contain an arbitrary number of values for each record. This is the field that I want to store in JSON column. The other fields are stored in statically defined columns with their respective types.

First attempt

BigQuery infer schema does not work with maps. So doing something like this won’t work.

1

schema, err := bigquery.InferSchema(Order{})

Even if you make the Attributes field type json.RawMessage it won’t work again.

Second attempt

Manually create the schema and try again.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

schema := bigquery.Schema{
    {Name: "id", Type: bigquery.StringFieldType},
    {Name: "item_id", Type: bigquery.StringFieldType},
    {Name: "category", Type: bigquery.StringFieldType},
    {Name: "timestamp", Type: bigquery.IntegerFieldType},
    {Name: "attributes", Type: bigquery.JSONFieldType},
}

// ... create the table

err := table.Inserter().Put(ctx, order)

This won’t work (with the Golang client) as BigQuery won’t convert the map to JSON. If we change the field type to json.RawMessage it won’t work again.

Third attempt is OK

We make the Order type implement the bigquery.ValueSaver interface and serialize the attributes field as JSON string.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

type OrderValueSaver struct {
    order *Order
}

// Save implements the bigquery.ValueSaver interface 
// to serialize the "attributes" field as JSON string.
func (s *OrderValueSaver) Save() (row map[string]bigquery.Value, insertID string, err error) {
    attr, err := json.Marshal(s.order.Attributes)
    if err != nil {
        return nil, "", err
    }

    row = map[string]bigquery.Value{
        "id":         s.order.ID,
        "item_id":    s.order.ItemID,
        "category":   s.order.Category,
		"timestamp":  s.order.Timestamp,
        "attributes": string(attr),
    }

    return row, s.order.ID, nil
}

func (c *Client) SaveOrder(ctx context.Context, order *Order) error {
    schema := bigquery.Schema{
        {Name: "id", Type: bigquery.StringFieldType},
        {Name: "item_id", Type: bigquery.StringFieldType},
        {Name: "category", Type: bigquery.StringFieldType},
        {Name: "timestamp", Type: bigquery.IntegerFieldType},
        {Name: "attributes", Type: bigquery.JSONFieldType},
    }
	
	// ... create/get table, ideally do it in the constructor of the client once 
	
	saver := &OrderValueSaver{order: order}
    
	return table.Inserter().Put(ctx, saver)
}