How to insert Data in JSONB Field of Postgres using GORM

Question

I have model like this in

• model.go

type yourTableName struct {
   Name             string `gorm:"type:varchar(50)" json:"name"`
   Email            string `gorm:"type:varchar(50)" json:"email"`
   FieldNameOfJsonb JSONB  `gorm:"type:jsonb" json:"fieldnameofjsonb"`
}

• I want to insert FieldNameOfJsonb as an array of object in postgres using GORM

• Like given below

{
    "name": " james",
    "email": "[email protected]",
    "FieldNameOfJsonb": [
        {
            "someField1": "value",
            "someFiedl2": "somevalue",    
        },
        {
            "Field1": "value1",
            "Fiedl2": "value2",
        }
    ],

Earnest

Answer 1

Just add this below code in Model.go (referenceLink)

import (
    "errors"
    "database/sql/driver"
    "encoding/json"
)

// JSONB Interface for JSONB Field of yourTableName Table
type JSONB []interface{}

// Value Marshal
func (a JSONB) Value() (driver.Value, error) {
    return json.Marshal(a)
}

// Scan Unmarshal
func (a *JSONB) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed")
    }
    return json.Unmarshal(b,&a)
}

-> reference Link for Marshal, Unmarshal

• now you can insert data using DB.Create(&yourTableName)

Earnest

I would change the type to: type JSONB map[string]interface{} – Hadji

obviously we can change as per our requirement – Earnest

Answer 2

I have answered a similar question in https://mcmap.net/q/1630983/-string-to-jsonb-with-gorm-and-postgres .

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.

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)

Spermatogonium

dsnt wok for me – Axle

Unfortunately, it doesn't work for me either – Conchology

any working solution for this? – Herein

Metadata pgtype.JSONB json:"-" gorm:"column:metadata" helps.. "type:jsonb" was causing issue for me.. remove it and it works :) – Herein

thanks, it worked for me, my type was a slice of int, and used the same code as its above – Monostome

Answer 3

You can use gorm-jsonb package.

Sepalous

Answer 4

I was able to make it work by simply adding serializer:json into the struct attribute, like so:

type yourTableName struct {
   Name             string `gorm:"type:varchar(50)" json:"name"`
   Email            string `gorm:"type:varchar(50)" json:"email"`
   FieldNameOfJsonb JSONB  `gorm:"type:jsonb;serializer:json" json:"fieldnameofjsonb"`
}

Hasidism