Golang scan db rows to json of string and array

698 views Asked by At

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;
0

There are 0 answers