Golang assign values to multidimensional struct from sql query

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?

I'm not sure I understand correctly. Does the parameter table contain policy name? Are there multiple policies in a table? — Koala Yeung
– Koala Yeung, Commented Oct 6, 2016 at 3:41
Yes, that doesn't make sense. I'll edit the question as I need to put the 'name' value in there. — Heath
– Heath, Commented Oct 7, 2016 at 0:51

Koala Yeung · Accepted Answer · 2016-10-07 08:02:19Z

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)
}

answered Oct 7, 2016 at 8:02

Koala Yeung

8,0284 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Heath Over a year ago

That's a method I haven't tried yet. Thank you. I will give it a go and mark your answer correct.

Collectives™ on Stack Overflow

Golang assign values to multidimensional struct from sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related