0

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;
4
  • In your database does a C object have only one A record and possibly multiple B records? Can you include the DB table definitions and the query you're using? Commented Dec 6, 2021 at 18:01
  • @HenryWoody updated the question with DB table and query Commented Dec 7, 2021 at 6:07
  • Does this answer your question? Postgres array of Golang structs Commented Dec 7, 2021 at 23:23
  • You just need to update the type of the B member in the C struct to []B, then the question I linked above should give you the rest. Commented Dec 7, 2021 at 23:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.