JSON marshall nullable type value in database/sql Go

Question

I am trying to figure out how one can properly marhsall nullable type (string, int, time) properly to JSON in Go. I know that database/sql provide sql.NullTime, sql.NullInt, etc but when you marshall these values, you get something like

{"first_name": {
  "Value": "",
  "Valid": false,
}}

What I really want is

{"first_name": null}

I understand that you can implement your own MarshalJSON to do this (I wrote about it here http://dennissuratna.com/marshalling-nullable-string-db-value-to-json-in-go/)

BUT I am wondering if anyone knows a better way to do this. I want to know other people know a less tedious way to do this.

tgio · Accepted Answer · 2016-07-25 10:29:56Z

3

May be late, but when searching for the same problem google report this page at a high rank, so : my solution is to define special type that surcharge NullInt64 for exemple and has and export JSON

Example for NullInt64 :

// NullInt64 is the same as sql.NullInt64
// But when exported to JSON it is null or the int64 val that is exported
// not a JSON containing Value and Valid properties
type NullInt64 struct {
    sql.NullInt64
}

// NullInt64 MarshalJSON interface redefinition
func (r NullInt64) MarshalJSON() ([]byte, error) {
    if r.Valid {
        return json.Marshal(r.Int64)
    } else {
        return json.Marshal(nil)
    }
}

And then replace all sql.NullInt64 by NullInt64. You can easily do the same for sql.NullString. Hope it helps

edited Jul 25, 2016 at 10:29

answered Jul 22, 2016 at 15:44

tgio

765 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

andybalholm · Accepted Answer · 2014-01-22 23:12:59Z

1

Create a type that embeds (e.g.) sql.NullInt and implements the json.Marshaler interface.

answered Jan 22, 2014 at 23:12

andybalholm

16.3k4 gold badges42 silver badges43 bronze badges

2 Comments

denniss Over a year ago

how is this different than what I said in the last paragraph? I do not want to implement my own MarshalJSON....

andybalholm Over a year ago

I was suggesting adding a MarshallJSON method to the individual fields (like sql.NullInt), not to the record as a whole.

Lars · Accepted Answer · 2024-03-01 07:34:25Z

I found https://github.com/volatiletech/null for nullable values in SQL and JSON.

Create a type with a nullable string like:

import "github.com/volatiletech/null/v9"
[...]

type Entry struct {
    Value  null.String  `json:"value"`
}

Set such string to null:

object := Entry{null.NewString("", false)}

After marshaling the object, it creates a JSON like

{
    "value": null 
}

Or set it to a string like:

object := Entry{null.NewString("Hello World", true)}

After marshaling the object, it creates a JSON like

{
    "value": "Hello World" 
}

Unmarshaling such JSON results in an object with a null.String in it. Due to the fact GO has no ternary operator it is a little bit "unnice" to get the value back.

entry := Entry{}

err := json.Unmarshal([]byte(<the JSON>), &entry)

if entry.Value.IsValid {
    return entry.Value.String
} else {
    return ""
}

Value.IsValid will return true, if the Value in the Entry object is not null.

publysher · Accepted Answer · 2014-01-23 07:18:08Z

0

If you really want to have null field, you will have to resort to a custom marshaller (or maybe, just maybe, use *string fields in struct and assign nil instead of an empty string).

However, if you look at the original goal of JSON (JavaScript Object Notation), you will notice that in JavaScript there is hardly any difference between:

var obj = JSON.parse('{"first_name": null }');
alert(obj.first_name)

and:

var obj = JSON.parse('{}');
alert(obj.first_name)

In other words: assigning null to a field has the same effect as not specifying it all. And most JSON parsers work like this.

Not specifying empty fields is supported by the Go JSON marshaller:

type MyType struct {
    firstname string `json:omitempty`
}

In the end, it depends on what you want to do with your JSON :)

answered Jan 23, 2014 at 7:18

publysher

11.4k1 gold badge25 silver badges28 bronze badges

3 Comments

denniss Over a year ago

Since I am getting the value of the struct form the DB, I am using sql.NullString as the type, not string. I am not sure how omitempty would apply to sql.NullString

publysher Over a year ago

Ah, I see. My first advice would be not to conflate the structs you use for database access with the structs you use for JSON marshalling, but that's probably not the kind of advice you're looking for.

denniss Over a year ago

Well! that's an approach! :)

Collectives™ on Stack Overflow

JSON marshall nullable type value in database/sql Go

4 Answers 4

Comments

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related