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"
}
]
}
]
}
]
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"}]}