How do you marshal a sql.NullString such that the output is flattened to give just the value in go?

Question

Given a go struct

type Company struct {
    ID   int             `json:"id"`              
    Abn  sql.NullString  `json:"abn,string"`
}

when marshalled with something like this

company := &Company{}
company.ID = 68
company.Abn = "SomeABN"
result, err := json.Marshal(company)

the result is

{
    "id": "68",
    "abn": {
        "String": "SomeABN",
        "Valid": true
    }
}

The result desired is

{
    "id": "68",
    "abn": "SomeABN"
}

I've tried explicitly stating that Abn is a string.

Abn  sql.NullString  `json:"abn,string"`

which did not change the result.

How do you marshal a sql.NullString such that the output is flattened to give just the value in go?

EDIT

Something like I ended up with after reading the answers from https://stackoverflow.com/users/8256506/nilsocket and https://stackoverflow.com/users/965900/mkopriva

package main

import (
    "database/sql"
    "encoding/json"
    "reflect"
    //"github.com/lib/pq"
)

/*
    https://medium.com/aubergine-solutions/how-i-handled-null-possible-values-from-database-rows-in-golang-521fb0ee267
*/

type NullString sql.NullString

func (x *NullString) MarshalJSON() ([]byte, error) {
    if !x.Valid {
        x.Valid = true
        x.String = ""
        //return []byte("null"), nil
    }
    return json.Marshal(x.String)
}

// Scan implements the Scanner interface for NullString
func (ns *NullString) Scan(value interface{}) error {
    var s sql.NullString
    if err := s.Scan(value); err != nil {
        return err
    }

    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *ns = NullString{s.String, false}
    } else {
        *ns = NullString{s.String, true}
    }

    return nil
}

type Company struct {
    ID                     int             `json:"id"`    
    Abn                    NullString      `json:"abn"`         
}

Swisher

Answer 1

Here is the code,

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
)

//Company details
type Company struct {
    ID  int        `json:"id"`
    Abn NullString `json:"abn"`
}

//NullString is a wrapper around sql.NullString
type NullString sql.NullString

//MarshalJSON method is called by json.Marshal,
//whenever it is of type NullString
func (x *NullString) MarshalJSON() ([]byte, error) {
    if !x.Valid {
        return []byte("null"), nil
    }
    return json.Marshal(x.String)
}

func main() {
    company := &Company{}
    company.ID = 68
    //create new NullString value
    nStr := sql.NullString{String: "hello", Valid: true}
    //cast it
    company.Abn = NullString(nStr)
    result, err := json.Marshal(company)
    if err != nil {
        log.Println(err)
    }
    fmt.Println(string(result))
}

Here is the blog post which explains it in detail.

Maple

Answer 2

You cannot, at least not using just sql.NullString and encoding/json.

What you can do is to declare a custom type that embeds sql.NullString and have that custom type implement the json.Marshaler interface.

type MyNullString struct {
    sql.NullString
}

func (s MyNullString) MarshalJSON() ([]byte, error) {
    if s.Valid {
        return json.Marshal(s.String)
    }
    return []byte(`null`), nil
}

type Company struct {
    ID   int          `json:"id"`              
    Abn  MyNullString `json:"abn,string"`
}

https://play.golang.org/p/Ak_D6QgIzLb

Praxis

See also medium.com/@raymondhartoyo/… – Jandel

Answer 3

Another option is to use *string instead of sql.NullString

type Company struct {
    ID   int      `json:"id"`              
    Abn  *string  `json:"abn"`
}

Now you may ask yourself what is the Difference between *string and sql.NullString

There's no effective difference. We thought people might want to use NullString because it is so common and perhaps expresses the intent more clearly than *string. But either will work. -- Russ Cox https://groups.google.com/g/golang-nuts/c/vOTFu2SMNeA/m/GB5v3JPSsicJ

Anorexia

Will *string work when scanning results from a SQL database call? – Swisher

Yes. I am using this approach instead of an "own" sql.NullString. – Anorexia

Answer 4

The question suggests that you want to expose your database structure as JSON (presumably REST-ish) API. Unless the project is going to have a short lifespan or the logic layer is trivial, such an approach is considered an antipattern. The internals (database structure) become coupled with the external interface (API) and may result in a high cost of making a change.

I'm attaching some reads as Google is full of tutorials on how to do the opposite:

https://lostechies.com/jimmybogard/2016/05/12/entities-arent-resources-resources-arent-representations/

https://thorben-janssen.com/dont-expose-entities-in-api/

Korn