CODEWITHSUNDEEP
mysqlgo
Arjun Malhotra
Arjun Malhotra

Reputation: 489

How can I access JSON column in mysql db from golang

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

Profile userInformation badge
https://ps.w.org/metronet-profile-picture/assets/icon-256x256.png?rev=2464419 {"name": "Suzan Collins", "points": 10000, "countryName": "Poland"} assets/batcherPage/gold.png

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

Upvotes: 5

Views: 3425

Answers (2)

Jackson Harry
Jackson Harry

Reputation: 323

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.

Upvotes: 0

Neenad
Neenad

Reputation: 911

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

Upvotes: 6

Related Questions