56

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?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Alex
  • 5,671
  • 9
  • 41
  • 81
  • 1
    You should explain what you mean by "not how it used to be when I used regular int64 and string" in your question. – kostya Oct 12 '15 at 02:35
  • 5
    you have tried using pointers to ints, yes? ie: var anint *int, then using &anint if its null the pointer will be nil. works fine with JSON encoding. this works with gorp for sure, haven't tested with straight sql package though – David Budworth Oct 12 '15 at 17:16

2 Answers2

80

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

James Henstridge
  • 42,244
  • 6
  • 132
  • 114
  • 6
    What a great solution! Thank you! – Alex Oct 12 '15 at 03:29
  • 3
    Maybe it's redundant to say, but so fantastic to have Golang Playgrounds to demonstrate stuff like this - really makes a huge difference for language adoption. Thanks for a great answer. – NSTJ Feb 16 '17 at 15:35
  • Thanks mate! This is exactly what I'm looking for – Nguyen DN Mar 08 '21 at 00:10
44

If you use the null.v3 package, you won't need to implement any of the marshal or unmarshal methods. It's a superset of the sql.Null structs and is probably what you want.

package main

import "gopkg.in/guregu/null.v3"

type Person struct {
    Name     string      `json:"id"`
    Age      int         `json:"age"`
    NickName null.String `json:"nickname"` // Optional
}

If you'd like to see a full Golang webserver that uses sqlite, nulls, and json you can consult this gist.

Stephen Wood
  • 1,257
  • 1
  • 12
  • 13
  • 9
    This is a massive time-saver. I thought I was going to have to write all of the marshaling code but this package works perfectly. – dimiguel Mar 31 '19 at 07:55