使用Mysql在Go中创建父子嵌套的json

Question

I am using mysql database with golang (go). Below is my db structure for treeview

ScreenID        ParentID        ScreenName

1                0               Home

2                0               Run Records

3                0               Requests

4                3               NDR

5                4               Add NDR Request

Struct I am using is:

type Screens struct {
    ProductID int
    ParentID   int
    ScreenName string
    Children []Screens
}

Below is my golang code

db, err := sql.Open("mysql", username + ":" + password + "@tcp(127.0.0.1:3306)/" + dbName)

rows, err := db.Query("call usp_select_screens(1)")

for rows.Next() {
err := rows.Scan(&screens.ProductID, &screens.ParentID, &screens.ScreenName)

I'm trying to store the query result as a json. I need guidance in taking the output and marshaling it into a json in the form of:

[  
     {  
        "ParentId":"0",
        "ScreenId":"1",
        "Name":"Home"
    },
    {  
        "ParentId":"0",
        "ScreenId":"2",
        "Name":"Run Records"
    },
    {  
        "ParentId":"0",
        "ScreenId":"3",
        "Name":"Requests",
        "Children":[  
            {  
                "Name":"NDR",
                "ScreenId":"4",
                "ParentId":"3",
                "Children":[  
                    {  
                        "Name":"Add NDR Request",
                        "ScreenId":"5",
                        "ParentId":"4"
                    }
                ]
            }
        ]
    }
]

Answer 1

Typically I assign flags to the struct and then you can easily use json.Marshal to convert the struct into json format.

Update your struct to something like:

type Screens struct {
    ParentID int      `json:"ParentId" db:"ParentID"`
    ScreenID int      `json:"ScreenId" db:"ScreenID"`
    Name     string   `json:"Name" db:"ScreenName"`
    Children []Screens `json:"Children,omitempty"`
}

Then scan data into struct: (not tested)

for rows.Next() {
    if err := rows.Scan(&screens); err != nil {
        // break and handle err
    }
} 

Then convert to JSON:

s, err := json.Marshal(screens)
if err != nil {
    // handle error
}
fmt.Println(string(s))

Result:

{"ParentId":0,"ScreenId":2,"Name":"Home","Children":[{"ParentId":3,"ScreenId":4,"Name":"NDR"}]}

Go Playground - https://play.golang.org/p/KgpC7tFJ_hR