I am trying to get the output from DB using an inner join with 3 tables say Table A and B.
Output Struct
type C struct {
A A `json:"A"`
B B `json:"B"`
SecID int64 `json:"section_id"`
SecName string `json:"section_name"`
}
type A struct {
AID int64 `json:"aid"`
Name string `json:"name"`
Des string `json:"des"`
Price string `json:"price"`
}
type B struct {
BID int64 `json:"bid"`
Answer string `json::answer"`
Score int16 `json:"score"`
}
DB query
var cs []C
rows, err := db.Query(sqlStatement, RequestBody.tID)
for rows.Next() {
var c C
err = rows.Scan(&c.A.ID, &c.A.Name, &c.A.Des, &c.A.Price, &c.A.Price, &c.B.ID, &c.B.Answer, &c.B.Score, &c.SecID, &c.SecName)
cs = append(cs, c)
The above code result in the following output:
[
{
"a": {
"aid": 1,
"name": "XXXXXX",
"description": "addd kdjd a jdljljlad",
"price": "10",
},
"section_id": 1,
"section_name": "personal details",
"b": {
"bid": 1,
"answer": "adfdf d fd d f",
"score": 0
}
},
{
"a": {
"aid": 1,
"name": "XXXXXX",
"description": "addd kdjd a jdljljlad",
"price": "10",
},
"section_id": 1,
"section_name": "personal details",
"b": {
"bid": 2,
"answer": "adfdf d fd d f",
"score": 10
}
}
]
But I am trying to merge field "b" in one single field with the list of dictionaries and writing "a" field only once as the values are repeated.
[
{
"a": {
"aid": 1,
"name": "XXXXXX",
"description": "addd kdjd a jdljljlad",
"price": "10",
},
"b": [
{
"section_id": 1,
"section_name": "personal details",
"bid": 1,
"answer": "adfdf d fd d f",
"score": 0
},
{
"section_id": 1,
"section_name": "personal details",
"bid": 2,
"answer": "adfdf d fd d f",
"score": 10
}
]
}
]
Tried changing the struct but doesn't seem to work. DB details: Table A (AID, Name, Des, Place) Table B (BID, Answer, Score)
Query:
select * from A a
inner join temp_table tt on tt.aid = a.aid
inner join B b on b.bid = tt.bid
where a.aid=1;