[]string to jsonb with Gorm and postgres

Question

I have a Go struct which contains a slice of strings which I'd like to save as a jsonB object in Postgres with GORM.

I've come accross a solution which requires to use the GORM specific type (postgres.Jsonb) which I'd like to avoid.

When I try to run the AutoMigrate with a slice in my model, it panics and won't start although when I wrap this slice in a struct (which I'm okay doing) it will run without error but won't create the column in postgres.

type User struct {
        gorm.Model
        Data []string `sql:"type:"jsonb"; json:"data"`
} //Panics

type User struct {
        gorm.Model
        Data struct {
            NestedData []string
        } `sql:"type:"jsonb"; json:"data"`
} //Doesn't crash but doesn't create my column

Has anyone been able to manipulate jsonb with GORM without using the postgres.Jsonb type in models ?

Yari · Accepted Answer · 2022-03-27 12:13:50Z

3

The simplest way to use JSONB in Gorm is to use pgtype.JSONB.

Gorm uses pgx as it driver, and pgx has package called pgtype, which has type named pgtype.JSONB.

If you have already install pgx as Gorm instructed, you don't need install any other package.

This method should be the best practice since it using underlying driver and no custom code is needed. It can also be used for any JSONB type beyond []string.

type User struct {
    gorm.Model
    Data pgtype.JSONB `gorm:"type:jsonb;default:'[]';not null"`
}

Get value from DB

u := User{}
db.find(&u)

var data []string

err := u.Data.AssignTo(&data)
if err != nil {
    t.Fatal(err)
}

Set value to DB

u := User{}

err := u.Data.Set([]string{"abc","def"})
if err != nil {
    return
}

db.Updates(&u)

answered Mar 27, 2022 at 12:13

Yari

1802 silver badges7 bronze badges

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

Comments

Kelsnare · Accepted Answer · 2020-02-26 05:48:11Z

0

Maybe:

type DataJSONB []string

func (dj DataJSONB) Value() (driver.Value, error) {
    return json.Marshal(dj)
}

func (dj *DataJSONB) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return fmt.Errorf("[]byte assertion failed")
    }

    return json.Unmarshal(b, dj)
}

// Your bit
type User struct {
    gorm.Model
    Data DataJSONB `sql:"type:"jsonb"; json:"data"`
}

answered Feb 26, 2020 at 5:48

Kelsnare

7055 silver badges12 bronze badges

1 Comment

frlzjosh Over a year ago

I get this error: struct field tag sql:"type:"jsonb";json:"studs" not compatible with reflect.StructTag.Get: key:"value" pairs not separated by spacesstructtag

robbieperry22 · Accepted Answer · 2020-02-25 21:40:05Z

-1

Define a new type:

type Data map[string]interface{}

And implement the Valuer and Scanner interfaces onto them, which allows the field to be converted to a value for the database, and scanned back into the field, respectively:

// Value converts into []byte
func (d Data) Value() (driver.Value, error) {
  j, err := json.Marshal(d)
  return j, err
}

// Scan puts the []byte back into Data
func (d *Data) Scan(src interface{}) error {
  source, ok := src.([]byte)
  if !ok {
    return errors.New("Type assertion .([]byte) failed.")
  }

  var i interface{}
  if err := json.Unmarshal(source, &i); err != nil {
    return err
  }

  *d, ok = i.(map[string]interface{})
  if !ok {
    return errors.New("Type assertion .(map[string]interface{}) failed.")
  }

  return nil
}

Then you can define your field in your model like this:

type User struct {
        gorm.Model
        Data Data `type: jsonb not null default '{}'::jsonb`
}

Using the underlying map[string]interface{} type is nice too, as you can Unmarshal/Marshal any JSON to/from it.

answered Feb 25, 2020 at 21:40

robbieperry22

2,2633 gold badges25 silver badges60 bronze badges

1 Comment

Arash Outadi Over a year ago

Does this still work? I have been trying this and I keep getting panic: invalid sql type SomeMapType (map) I have an associated question here: stackoverflow.com/questions/63289749/…

Collectives™ on Stack Overflow

[]string to jsonb with Gorm and postgres

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related