Postgres在Golang中嵌套JSON结果 [英] Nested JSON result in Golang from Postgres

问题描述

我正在使用 gin 和 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

结果:

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
}

并使用以下命令返回结果:c.JSON(200, gin.H{"data": projects})

And returning the results using: c.JSON(200, gin.H{"data": projects})

只有一个项目才有效

但是，如果有多个项目，则会出现以下错误:

But if there's more than one project it gives the below error:

错误:json: error calling MarshalJSON for type json.RawMessage: invalid character '"' after top-level value

有什么建议吗?

P.S:我是Golang的新手

P.S: I'm a newbie in Golang

推荐答案

，您可以使用此站点 http://json2struct. mervine.net/根据结果获得正确的结构.只需复制选择结果，并生成体面的结构

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

或者您可以产生具有Project struct数组的新类型:

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

type Projects []Project

这篇关于Postgres在Golang中嵌套JSON结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！