Nested JSON result in Golang from Postgres

Question

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

Rhe methods on json.RawMessage all take a pointer receiver. Did you try to add Apps *json.RawMessage ...? — molivier
– molivier, Commented Mar 17, 2016 at 7:39

ugurozgen · Accepted Answer · 2016-03-17 09:46:50Z

4

you can use this site http://json2struct.mervine.net/ to get right struct according to result. just copy select result, and generate your decent struct

or you can produce new type which have Project struct array:

type Projects []Project

answered Mar 17, 2016 at 9:46

ugurozgen

819 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 17, 2016 at 7:34

Omar Masad

1551 gold badge4 silver badges18 bronze badges

1 Comment

coussej Over a year ago

Hi, I wrote a blog a few weeks ago on handling JSON(B) coming from postgres. It's indeed about creating your own type to handle the JSON. In my case I converted it into a map[string]interface{}, which I suppose would be handled by GIN correctly as well. coussej.github.io/2016/02/16/Handling-JSONB-in-Go-Structs

Rajesh · Accepted Answer · 2022-10-28 07:27:23Z

0

I used the below link https://www.alexedwards.net/blog/using-postgresql-jsonb

and http://json2struct.mervine.net/

to make it work . I was struggling for long time and then realized that the struct did the trick.

answered Oct 28, 2022 at 7:27

Rajesh

1001 silver badge4 bronze badges

Collectives™ on Stack Overflow

Nested JSON result in Golang from Postgres

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related