Golang从SQL查询中将值分配给多维结构

Question

I want to assign values returned from an SQL query to a multidimensional struct. I want to output this multidimensional struct as JSON to a Web frontend.

Edit: SQL query is simple, 'SELECT * FROM policy'. It returns many key/values but for the purpose of the example I'm just using the 'policy_id', 'class_id' and 'name' fields.

My Structs

    type table struct {
    Policy string `json:"policy"`
    P []Parameters `json:"parameters"`
    }

    type Parameters struct {
    Policy_id string `json:"policy_id"`
    Class_id string `json:"class_id"`
    Name string `json:"name"`
    .... <removed many more entries of the same type>
    }

I then loop through the SQL output using rows.Next() // not important for the question.

If I do:

p := Parameters{}

then:

err = rows.Scan(&p.Policy_id, &p.Class_id, &p.Name)

p is populated with the key / values pairs I'd expect.

Edit: I added p.Name as I want to use the Name returned from the query string as the unique key for the table struct. This wasn't clear before.

However, I want to get the data into the higher level struct 'table'. I eventually want the output to look something like this in JSON:

{policy: "policyName1", P: [{policy_id: '1', class_id:'01"},{policy_id: '2', class_id: '02'}, etc]}

I've tried things like this:

t := table {
Policy: p.Name,
P: p,
}

And a few other combinations, but at this point I'm just guessing. Can someone point me in the right direction?

Answer 1

Probably something like this:

// a map with policy name as key
ts := map[string]tables

defer rows.Close()
for rows.Next() {
    p := Parameters{}
    if err := rows.Scan(&p.Policy_id, &p.Class_id, &p.Name); err != nil {
        log.Fatal(err)
    }

    // initialize the table, if not exists
    if _, ok := ts[p.Name]; !ok {
        ts[p.Name] = table{
            Policy: p.Name,
            P: make([]Parameters),
        }
    }

    // append the parameters to the policy
    ts[p.Name].P = append(ts[p.Name].P, p)
}