How can I work with SQL NULL values and JSON in a good way?

Question

Go types like Int64 and String cannot store null values, so I found I could use sql.NullInt64 and sql.NullString for this.

But when I use these in a Struct, and generate JSON from the Struct with the json package, then the format is different to when I use regular Int64 and String types.

The JSON has an additional level because the sql.Null*** is also a Struct.

Is there a good workaround for this, or should I not use NULLs in my SQL database?

Answer 1

Types like sql.NullInt64 do not implement any special handling for JSON marshaling or unmarshaling, so the default rules apply. Since the type is a struct, it gets marshalled as an object with its fields as attributes.

One way to work around this is to create your own type that implements the json.Marshaller / json.Unmarshaler interfaces. By embedding the sql.NullInt64 type, we get the SQL methods for free. Something like this:

type JsonNullInt64 struct {     sql.NullInt64 }  func (v JsonNullInt64) MarshalJSON() ([]byte, error) {     if v.Valid {         return json.Marshal(v.Int64)     } else {         return json.Marshal(nil)     } }  func (v *JsonNullInt64) UnmarshalJSON(data []byte) error {     // Unmarshalling into a pointer will let us detect null     var x *int64     if err := json.Unmarshal(data, &x); err != nil {         return err     }     if x != nil {         v.Valid = true         v.Int64 = *x     } else {         v.Valid = false     }     return nil } 

If you use this type in place of sql.NullInt64, it should be encoded as you expect.

You can test this example here: http://play.golang.org/p/zFESxLcd-c