mysql JSON_SET cannot be used in many ways

[ZiRunHua]

I know there are many disadvantages to using json, but in some cases it can improve development efficiency.

I happened to use JSON_SET in a scenario but found that it did not work properly and I tried several methods without success. Considering that JSON_SET can modify json fields without querying the database, it would be very bad if JSON_SET could not be used in future work, so I submitted this issue.

Env

go 1.20

mysql:8.0.22

gorm.io/driver/mysql v1.5.7
gorm.io/gen v0.3.26
gorm.io/gorm v1.25.11
github.com/go-sql-driver/mysql v1.8.1

Executable sample code

It's also a simplified version of my business code

var db *gorm.DB

func init() {
    db = initialize.MDB //init db
    db.AutoMigrate(&Transaction{}, &TransactionInfo{}, &TransactionTiming{})
}

type Transaction struct {
    ID uint `gorm:"primarykey"`
    TransactionInfo
    gorm.Model
}

type TransactionInfo struct {
    UserId, AccountId, CategoryId uint
    Amount                        int
    Remark                        string
    TradeTime                     time.Time
}

type TransactionTiming struct {
    ID         uint `gorm:"primarykey"`
    AccountId  uint `gorm:"index"`
    UserId     uint
    TransInfo  TransactionInfo `gorm:"not null;type:json;serializer:json"`
    OffsetDays int             `gorm:"not null;"`
    NextTime   time.Time       `gorm:"not null;"`
    Close      bool
    gorm.Model
}

type UpdateNextTime func(timing TransactionTiming, db *gorm.DB, nextTime time.Time) error

func TestUpdate(t *testing.T) {
    var handle UpdateNextTime

    t.Log("-----v1------")
    handle = func(timing TransactionTiming, db *gorm.DB, nextTime time.Time) error {
        s := "Update transaction_timing set next_time = ? , trans_info = JSON_SET(`trans_info`,'$.trade_time',?) WHERE id  = ?"
        return db.Exec(s, nextTime.Format("2006-01-02 15:04:05"), nextTime.Format("2006-01-02 15:04:05"), timing.ID).Error
    }
    runTest(handle, t)
    t.Log("-----v2------")
    handle = func(timing TransactionTiming, db *gorm.DB, nextTime time.Time) error {
        return db.Model(timing).Updates(map[string]interface{}{
            "trans_info": datatypes.JSONSet("trans_info").Set("trade_time", nextTime),
            "next_time":  nextTime,
        }).Error
    }
    runTest(handle, t)
    t.Log("-----v3------")
    handle = func(timing TransactionTiming, db *gorm.DB, nextTime time.Time) error {
        return db.Model(timing).Updates(map[string]interface{}{
            "trans_info": gorm.Expr("JSON_SET(`trans_info`,'$.trade_time',?)", nextTime),
            "next_time":  nextTime,
        }).Error
    }
    runTest(handle, t)
}

func runTest(handle UpdateNextTime, t *testing.T) {
    timing := TransactionTiming{TransInfo: TransactionInfo{TradeTime: time.Now()}, NextTime: time.Now()}
    db.Create(&timing)
    t.Log(fmt.Sprintf("id:%d trade_time:%v next_time:%v", timing.ID, timing.TransInfo.TradeTime, timing.NextTime))
    err := handle(timing, db, time.Now().Add(time.Hour))
    if err != nil {
        t.Error(err)
    }
    db.First(&timing, timing.ID)
    t.Log(fmt.Sprintf("id:%d trade_time:%v next_time:%v", timing.ID, timing.TransInfo.TradeTime, timing.NextTime))
}

[github-actions[bot]]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[ZiRunHua]

Oh, I found the reason. change datatypes.JSONSet("trans_info").Set("trade_time", nextTime) to datatypes.JSONSet("trans_info").Set("TradeTime", nextTime). I forgot I was working on a json string.