How can I access JSON column in mysql db from golang

Question

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

Not sure about MySQL, but try reading the JSON column into a string when you run the query, then have a separate step to json.Unmarshal each of those strings into their fields — Ben
– Ben, Commented Jul 4, 2022 at 4:34
Maybe JSON parsing in the query will solve your problem? SELECT Profile, userInformation->>name AS name, userInformation->>points AS points, userInformation->>countryName AS countryName, badge FROM Batchers_page_db, and then something like rows.Scan(&batcher.ProfileURL, &batcher.name, &batcher.points, &batcher.countryName, &batcher.Badge);? — Akina
– Akina, Commented Jul 4, 2022 at 4:45

Neenad · Accepted Answer · 2022-07-04 04:59:36Z

6

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

answered Jul 4, 2022 at 4:59

Neenad

9075 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jackson Harry · Accepted Answer · 2022-07-04 06:03:11Z

0

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.

answered Jul 4, 2022 at 6:03

Jackson Harry

3231 gold badge3 silver badges16 bronze badges

Collectives™ on Stack Overflow

How can I access JSON column in mysql db from golang

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related