Nested JSON result in Golang from Postgres

[zero...@gmail.com]

down votefavorite

I'm using gin and gorp The SQL: SELECT p.project_id, p.name, COALESCE(NULLIF(json_agg(a.*)::TEXT, '[null]'), '[]')::JSON AS apps FROM project p LEFT JOIN app a USING (project_id) WHERE p.user_id=19 GROUP BY p.project_id, p.name ORDER BY project_id The results:  Golang type Project struct { ID int64 `db:"project_id, primarykey, autoincrement" json:"id"` UserID int64 `db:"user_id" json:"user_id"` Name string `db:"name" json:"name"` Status int `db:"status" json:"status"` UpdatedAt int64 `db:"updated_at" json:"updated_at"` CreatedAt int64 `db:"created_at" json:"created_at"` Apps json.RawMessage `json:"apps"` } func GetProjects(userID int64, page string) []Project { var projects []Project var err error _, err = db.GetDB().Select(&projects, "SELECT p.project_id, p.name, COALESCE(NULLIF(json_agg(a.*)::TEXT, '[null]'), '[]')::JSON AS apps FROM project p LEFT JOIN app a USING (project_id) WHERE p.user_id=$1 GROUP BY p.project_id, p.name ORDER BY project_id LIMIT 10 OFFSET $2", userID, page) fmt.Println("err", err) return projects } And returning the results using: c.JSON(200, gin.H{"data": projects}) It works if there's only one project But if there's more than one project it gives the below error: The error: json: error calling MarshalJSON for type json.RawMessage: invalid character '"' after top-level value Any suggestions? P.S: I'm a newbie in Golang The question is also here: http://stackoverflow.com/q/36053251/689158

[Omar Massad]

I made it work using this solution below from this answer

I don't know how clean of a solution this is but I ended up making my own data type JSONRaw. The DB driver sees it as a []btye but it can still be treated like a json.RawMessage in the Go Code.

This is a copy paste reimplementation of MarshalJSON and UnmarshalJSON from the encoding/json library.

//JSONRaw ...
type JSONRaw json.RawMessage

//Value ...
func (j JSONRaw) Value() (driver.Value, error) {
    byteArr := []byte(j)

    return driver.Value(byteArr), nil
}

//Scan ...
func (j *JSONRaw) Scan(src interface{}) error {
    asBytes, ok := src.([]byte)
    if !ok {
        return error(errors.New("Scan source was not []bytes"))
    }
    err := json.Unmarshal(asBytes, &j)
    if err != nil {
        return error(errors.New("Scan could not unmarshal to []string"))
    }

    return nil
}

//MarshalJSON ...
func (j *JSONRaw) MarshalJSON() ([]byte, error) {
    return *j, nil
}

//UnmarshalJSON ...
func (j *JSONRaw) UnmarshalJSON(data []byte) error {
    if j == nil {
        return errors.New("json.RawMessage: UnmarshalJSON on nil pointer")
    }
    *j = append((*j)[0:0], data...)
    return nil
}

//Project ....

type Project struct {
    ID        int64   `db:"project_id, primarykey, autoincrement" json:"id"`
    UserID    int64   `db:"user_id" json:"user_id"`
    Name      string  `db:"name" json:"name"`
    Status    int     `db:"status" json:"status"`
    UpdatedAt int64   `db:"updated_at" json:"updated_at"`
    CreatedAt int64   `db:"created_at" json:"created_at"`

    Apps      JSONRaw `json:"apps"`
}

But I was wondering if there's a clean way other than this?

Hope this also help others.

[Tamás Gulácsi]

Do not use C.JSON. I don't know gin, but just set the Content-Type to application/json, and send the returned text as is (already marshaled, by PostgreSQL).