Skip to content

Instantly share code, notes, and snippets.

@yanmhlv
Created February 8, 2016 14:49
Show Gist options
  • Save yanmhlv/d00aa61082d3b8d71bed to your computer and use it in GitHub Desktop.
Save yanmhlv/d00aa61082d3b8d71bed to your computer and use it in GitHub Desktop.
JSONB in gorm
package main
import (
"database/sql/driver"
"encoding/json"
"github.com/jinzhu/gorm"
_ "github.com/lib/pq"
)
type JSONB map[string]interface{}
func (j JSONB) Value() (driver.Value, error) {
valueString, err := json.Marshal(j)
return string(valueString), err
}
func (j *JSONB) Scan(value interface{}) error {
if err := json.Unmarshal(value.([]byte), &j); err != nil {
return err
}
return nil
}
type User struct {
gorm.Model
Info JSONB `sql:"type:jsonb"`
}
func main() {
db, _ := gorm.Open("postgres", "user=myuser password=mypassword dbname=mydbname sslmode=disable")
db.CreateTable(&User{})
db.Create(&User{Info: JSONB{"age": 27, "name": "Yan"}})
}
@hewenyu
Copy link

hewenyu commented Jun 15, 2021

This is very useful.

@kachar
Copy link

kachar commented Jun 15, 2021

Looks like it's a bit outdated as latest gorm version uses different annotation gorm:"type:jsonb" instead of sql:"type:jsonb" and this makes all marshaled values to be null

@bjornmolin
Copy link

Thank you so much! 🌷 return string(valueString), err solved my problem with ERROR: invalid input syntax for type json (SQLSTATE 22P02)

I have spent hour if not days in order to find out this

@encryptblockr
Copy link

still valid after 7 years? wow

@encryptblockr
Copy link

encryptblockr commented Aug 7, 2022

how do we query the JSONB? anyone willing to share their gist on how to do JSONB queries also using GORM?

@kachar @bjornmolin

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment