Gorm: If you want to support JSON column for mysql

[chalince]

1. Add new type named JSON and rewrite methods:

package models
import (
    "bytes"
    "errors"
    "database/sql/driver"
)
type JSON []byte
func (j JSON) Value() (driver.Value, error) {
    if j.IsNull() {
        return nil, nil
    }
    return string(j), nil
}
func (j *JSON) Scan(value interface{}) error {
    if value == nil {
        *j = nil
        return nil
    }
    s, ok := value.([]byte)
    if !ok {
        errors.New("Invalid Scan Source")
    }
    *j = append((*j)[0:0], s...)
    return nil
}
func (m JSON) MarshalJSON() ([]byte, error) {
    if m == nil {
        return []byte("null"), nil
    }
    return m, nil
}
func (m *JSON) UnmarshalJSON(data []byte) error {
    if m == nil {
        return errors.New("null point exception")
    }
    *m = append((*m)[0:0], data...)
    return nil
}
func (j JSON) IsNull() bool {
    return len(j) == 0 || string(j) == "null"
}
func (j JSON) Equals(j1 JSON) bool {
    return bytes.Equal([]byte(j), []byte(j1))
}

1. Use new JSON type:

package models
type Model struct {
    ID        int   `gorm:"primary_key" json:"id"`
    CreatedAt int64 `json:"createdAt"`
    UpdatedAt int64 `json:"updatedAt"`
    Object    JSON  `sql:"type:json" json:"object,omitempty"`
}

[chalince]

@jinzhu
I test it success. Maybe you can tell others.

[bruinxs]

JSON.Value, JSON.Scan, JSON.IsNull, JSON.Equals. What are these functions and are they tool methods?

[JoVzla]

@Chalin-Shi Thanks for the solution, i was about to drop the use of gorm because of the JSON Support, this must be included in the library.

[trilopin]

@Chalin-Shi could you help with a read/write example?

I have no problems for writing but when I read data is not automatically unserialized.

Thanks!

[chalince]

@trilopin It's my pleasure. I will give an example for you:

// ~/models/json.go
// use the top file

// ~/models/app.go
package models

type App struct {
    Name    string `sql:"not null" json:"name,omitempty" binding:"required"`
    Icon    JSON   `sql:"type:json" json:"icon"`
}

func GetApp(id int) (app App) {
    db.Where("id = ?", id).First(&app)

    return
}

func EditApp(id int, data interface{}) bool {
    db.Model(&App{}).Where("id = ?", id).Updates(data)

    return true
}


// ~/controllers/app.go
package app

func AddIcon(c *gin.Context) {
    id := com.StrTo(c.Param("id")).MustInt()

    type Img struct {
        Name string `json:"name"`
        Link string `json:"link"`
    }
    img := &Img{"golang.png", "https://www.domain.com/avatar.png"}

    icon, _ := json.Marshal(img)
    data := map[string]interface{}{"icon": icon}

    models.EditApp(id, data)
    return nil
}

func GetApp(c *gin.Context) {
    var app models.App
    id := com.StrTo(c.Param("id")).MustInt()
    app = models.GetApp(id)
    return app
}

[chalince]

@trilopin , Hi, buddy. Have you resolved your problem?

[oxfordyang2016]

@but it cannot generate a column name in table

[retailify]

@Chalin-Shi I think there is a bug in the Scan Method

        if !ok {
        errors.New("Invalid Scan Source")
    }

It should

         if !ok {
        return errors.New("Invalid Scan Source")
    }

[pingkui]

how to fixed json array

[zphilin]

Nice！

[pyaesone17]

I think it would be nice included by default

[chmorgan]

Any update on this being included in Gorm? I have a json field and I can’t figure out how to create a struct with a field that creates this type of field...

[chmorgan]

@jinzhu what would it take to get the json type supported or does it make more sense to just use the Gorm type option like:

struct mytable {
    JsonColumn     string     `gorm:"type:json"`
}

?

[aight8]

The nicest where:

type User struct {
   EmbeddedJSONObject UserFlags `gorm:"type:json"`
   EmbeddedJSONArray []UserFlags `gorm:"type:json"`
}
type UserFlags struct {
   FlagA bool
   FlagB string
   Other OtherNestedThing
}
type OtherNestedThing struct{}

So you using the field as a struct in code. But it's saved as JSON. This would be awesome.
Would work nice together with request data binders, validators etc.

[dickmanben]

@aight8

So you using the field as a struct in code. But it's saved as JSON. This would be awesome.
Would work nice together with request data binders, validators etc.

You can accomplish this by using json.Unmarshal and grab the []byte value of the JSON field.

It's one extra step but not very difficult.

[saraswat40]

+1 for @aight8. @dickmanben, in your solution one would need to maintain another type ( or at least similar fields in the same type) and translate while getting/setting. If we go with @aight8 solution, it is much more elegant.

[dickmanben]

@saraswat40 Doesn't @aight8 also have another type to maintain?

[saraswat40]

no, in the requested/ideal solution only one type like this will be needed:

type User struct {
   EmbeddedJSONObject UserFlags `gorm:"type:json"`
}

However, currently two types need to be maintained with json.marshal/unmarshall needed to translate between two types on get/set, like this:

type User struct {
   EmbeddedJSONObject UserFlags
 }

 type DBUser struct {
    EmbeddedJSONObject []byte `gorm:"type:json"`
 }

[dickmanben]

I see what you mean now. I was suggesting that at the point of access for EmbeddedJSONObject you could unmarshal that into the UserFlag struct, if only for a workaround until a solution is in place to store a type as json, which of course would be the most ideal solution.

[aight8]

I done it this way currently. I have many custom types, the scan/value method is almost copied boilerplate code.
Same for more abstract types. A custom type based on string array.
Once this json serialization gets native it get‘s much more elegant and can delete a bunch of repeated code.

Beside that, some popular form encoder/validator base on reflected types. So a custom type fn must be implemented there because my custom StringArray is not treated automatically as []string.

[ikaven1024]

i wrapper slice into a struct. Objs .Data get the object. see blow:

type Objs struct {
    Date []Object
}

func (o Objs ) Value() (driver.Value, error) {
    b, err := json.Marshal(o.Data)
    return string(b), err
}

func (o *Objs ) Scan(input interface{}) error {
    return json.Unmarshal(input.([]byte), o.Data)
}

[colorworlds]

Maybe it is a bug in the Scan Method,my version:

func (j *JSON) Scan(value interface{}) error {
    if value == nil {
        *j = nil
        return nil
    }
    str, ok := value.(string)  //i find value should be a string in my app ,not a []byte

    if !ok {
        return errors.New("Invalid Scan Source")
    }
    s := []byte(str)
    *j = append((*j)[0:0], s...)
    return nil
}

[idoberko2]

I spent a couple of hours on @colorworlds's issue until I figured it out, and only then saw that comment 🤦🏻‍♂️

[Akagi201]

Maybe it is a bug in the Scan Method,my version:

func (j *JSON) Scan(value interface{}) error {
  if value == nil {
      *j = nil
      return nil
  }
  str, ok := value.(string)  //i find value should be a string in my app ,not a []byte

  if !ok {
      return errors.New("Invalid Scan Source")
  }
  s := []byte(str)
  *j = append((*j)[0:0], s...)
  return nil
}

But mine is []byte not string

[jinzhu]

https://github.com/go-gorm/datatypes for v2