Empty map routing [string] interface {} results in "null" instead of nil
I have a problem where Go inserts "null" into my PostgreSQL database jsonb columns if I try to do the following and the structs property (in this case the type [string] interface) {}) is empty:
accessMembers, _ := json.Marshal(c.AccessMembers)
When doing a test print, it outputs the same as the value stored in the database:
fmt.Println(string(accessMembers)) // equals the string "null"
The problem is that I need it to be nil (not a string, but Golang nil), so when I use it in the Exec function below:
sqlStr := `UPDATE my_table SET access_members=$1 WHERE id=$2;`
_, err := db.Exec(sqlStr, accessMembers, c.Id)
It should be equivalent (which works !!):
_, err := db.Exec(sqlStr, nil, c.Id)
I've tried many different workarounds that I thought should work, but they didn't.
eg.
var accessMembers []byte
am, _ := json.Marshal(c.AccessMembers)
if string(am) != "null"{
accessMembers = am
}
sqlStr := `UPDATE my_table SET access_members=$1 WHERE id=$2;`
_, err := db.Exec(sqlStr, accessMembers, c.Id)
This results in the following error: "pq: invalid input syntax for json type"
I can't figure out why this is not the same as explicitly specifying nil in the parameter of the Exec functions, since the [] byte must obviously be nil, right?
What am I doing wrong here? This is how I hoped it would work: http://play.golang.org/p/UoLAGfhhRl
Answer:
var accessMembers interface{} = nil
if c.AccessMembers != nil {
j, _ := json.Marshal(c.AccessMembers)
accessMembers = j
}
Thanks to thwd for providing a solution mostly and for specifying how the database driver marshals differently by offering an interface {} instead of a [] byte.
Thanks DonSeba for reminding me to check if the struct exists before using the resources for marshal to do string conversion and comparison, which should be more expensive.
source to share
I can't figure out why this is not the same as explicitly specifying nil in the parameter of the Exec functions, since the [] byte must obviously be nil, right?
nil
has a type. Database driver routers are nil
different for type map[string]interface{}
or []byte
than for interface{}
(which is the type of the second argument db.Exec
). You should be able to:
var i interface{} = nil
j, _ := json.Marshal(c.AccessMembers)
if string(j) != "null" {
i = j
}
sqlStr := `UPDATE content SET access_members=$1 WHERE id=$2;`
_, err := db.Exec(sqlStr, i, c.Id)
source to share
You haven't initialized yours AccessMembers
.
Before you decide to marshal a value, you must check if it exists.
if c.AccessMembers == nil {
c.AccessMembers = make(map[string]interface{})
}
If you do this, the result is:
accessMembers, err := json.Marshal(c.AccessMembers)
will be a valid empty json value {}
and will be valid to be stored in db and later retrieved.
UPDATE:
the "NULL" value was discussed here: https://code.google.com/p/go/issues/detail?id=2278
source to share