I have a table in mysql with 3 columns Profile string, userInformation JSON, badge string.

This is my struct:

type BatchersData struct {
    ProfileURL      string          `json:"profileUrl"`
    UserInformation UserInformation `json:"userInformation"`
    Badge           string          `json:"badge"`
}
type UserInformation struct {
    Points      int64  `json:"points"`
    Name        string `json:"name"`
    CountryName string `json:"countryName"`
}

What I want to do is make a select query on this table ie GET and retrieve every information..

using this code, I have accessed Profile and Badge :

func getBatchers(c *gin.Context) {

    var batchers []BatchersData
    rows, err := db.Query("SELECT profileUrl, badge FROM Batchers_page_db")

    if err != nil {
        return
    }
    defer rows.Close()
    for rows.Next() {
        var batcher BatchersData
        if err := rows.Scan(&batcher.ProfileURL, &batcher.Badge); err != nil {
            return
        }
        batchers = append(batchers, batcher)
    }
    if err := rows.Err(); err != nil {
        return
    }
    c.IndentedJSON(http.StatusOK, batchers)
}

But I want to access JSON column ie UserInformation as well. I know that the query will be

rows, err := db.Query("SELECT * FROM Batchers_page_db")

But i'll have to make a change in this statement

if err := rows.Scan(&batcher.ProfileURL, &batcher.Badge);

I have tried doing this : but nothing works

rows.Scan(&batcher.ProfileURL,&batcher.UserInformation, &batcher.Badge);

CodePudding user response：

You need to implement Scan interface doc to map the data to JSON. Here try this:

func (u * UserInformation) Scan(value interface{}) error {
  b, ok := value.([]byte)
  if !ok {
    return errors.New("type assertion to []byte failed")
  }
  return json.Unmarshal(b, &u)
}

CodePudding user response：

SELECT JSON_EXTRACT(userInformation,'$.name') as profileName, JSON_EXTRACT(userInformation,'$.points') as userPoints from Batchers_page_db

Not tested, but something like this will work but make sure your database field must be JSON type.