How to Handle SQL NULL Values in JSON using Go Type Conversions?

JSON Handling of SQL NULL Values with Go Type Conversions

While Go types like Int64 and String cannot store null values, using sql.NullInt64 and sql.NullString allows for this capability. However, encoding these types as JSON using the json package results in an altered format compared to regular Int64 and String types.

This discrepancy arises because sql.Null* types are themselves structs, causing an additional level of nesting in the JSON representation. To overcome this, one can implement custom types adhering to the json.Marshaller and json.Unmarshaler interfaces.

For instance, the JsonNullInt64 type can be defined, embedding the sql.NullInt64 type and implementing custom JSON handling:

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 {
    // Pointer unmarshalling ensures NULL detection
    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
}

Substituting sql.NullInt64 with JsonNullInt64 results in the desired JSON encoding. For further testing, refer to this playground: http://play.golang.org/p/zFESxLcd-c

The above is the detailed content of How to Handle SQL NULL Values in JSON using Go Type Conversions?. For more information, please follow other related articles on the PHP Chinese website!