How to Handle NULL Values in JSON and SQL Effectively with Go?

JSON and NULL Values in SQL with Go

Handling NULL values in SQL databases can be a challenge, especially when working with JSON. In Go, types like Int64 and String do not inherently support NULL values, giving rise to the need for wrapper types like sql.NullInt64 and sql.NullString.

However, when these wrapper types are used within structs and JSON is generated using the json package, the resulting JSON format deviates from what is expected due to the additional nesting introduced by the wrapper's struct nature.

Addressing the Issue

To overcome this issue, one viable solution is the creation of custom types that adhere to the json.Marshaller and json.Unmarshaler interfaces. By embedding the sql.NullInt64 type, the benefits of SQL methods are retained while simultaneously customizing JSON handling. Here's an example:

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 detects null values
    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
}

By using the custom JsonNullInt64 type in place of sql.NullInt64, JSON encoding aligns with expectations.

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