How to write table data into a nested struct in Go

Question

Summary

I am trying to write data from several postgres tables into a nested Go struct for the purpose of returning a single json response to a GET request within my web app.

Questions

1. Is the way I'm declaring a nested struct reasonable from a Go best practices perspective, or is there a reason I should avoid this method and do it another way?
2. What am I doing wrong in Step 3 to prevent my code from working? (I fear the answer is 'everything')

What I've got so far

1. I've declared my struct of structs

type MainObject struct {

    SubObjects []struct {
        SpecificDetail string `json:"specific-detail"`
                           }  `json:"sub-object"`

    ...(other []structs)...
}

2. I've retrieved rows from the tables

func getMainObjectHandler(w http.ResponseWriter, r *http.Request) {
    
    ...(database connnection)...
    
    MainObjectID := r.URL.Query().Get("moid")
    if MainObjectID != "null" {
        NewMainObject := MainObject{}
        SubObjectDetail_rows, err := db.Query("SELECT specific_detail from the_table WHERE moid= '" + MainObjectID + "'")
        if err != nil {
            log.Fatalf("could not execute query: %v", err)
        }
        
        ...(other db.Query rows)...

3. I've tried (and failed) to build the row data into the struct.

        for SubObjectDetail_rows.Next() {
            SpecificDetail := NewMainObject.SubObject.SpecificDetail{}
            SubObjectDetail_rows.Scan(&SpecificDetail)
            SubObject = append(SubObject, SpecificDetail)
        }
        NewMainObject = append(MainObject, SubObject)
        defer persona_rows.Close()

4. Finally, I've set up Marshal and write.

        NMOListBytes, err := json.Marshal(NewMainObject)
        if err != nil {
            fmt.Println(fmt.Errorf("Error: %v", err))
            w.WriteHeader(http.StatusInternalServerError)
            return
        }
        w.Write(NMOListBytes)

Answer 1

Firstly, please use placeholders when creating your SQL query to avoid injections:

// db.Query("SELECT specific_detail from the_table WHERE moid= '" + MainObjectID + "'")  // not this
db.Query("SELECT specific_detail from the_table WHERE moid=?", MainObjectID)

Unless you're using a framework like GORM you can't Scan() into a single struct value. From the docs:

Scan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in Rows.

It looks like you're pulling JSON from a DB query, as you're only querying one column, so you probably want:

var bs []byte // get raw JSON bytes

err = SubObjectDetail_rows.Scan(&bs) 
if err != nil { /* always check errors */ }

and then unmarshal them to your struct:

err = json.Unmarshal(bs, &SpecificDetail)
if err != nil { /* ... */ }