Golang GORM jsonb欄位struct轉換

GORM查詢PostgreSQL的jsonb欄位的struct轉換。

範例環境：

• Go 1.19
• GORM 2.0 (gorm.io/gorm v1.24.2)
• PostgreSQL 14

範例

PostgreSQL有employee資料表如下，contact欄位型態為jsonb：

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   contact jsonb,
   create_at timestamp NOT NULL
);

employee與GORM對應的model為Employee struct。

// employee.contact要轉換的型態
type Contact struct {
    Name  string
    Phone string
}

// 與employee對應的model
type Employee struct {
    ID        int64
    Name      string
    Age       int
    Contact   Contact // mapping to column `employee.contact`
    CreatedAt time.Time
}

GORM新增查詢時需要將employee.contact jsonb欄位與Contact做轉換，可透過實作Scanner與Valuer介面來自訂轉換邏輯。

GORM查詢時會呼叫Scanner.Scan將jsonb轉為自訂型態；
新增時會呼叫Valuer.Value將自訂型態轉為jsonb。

若jsonb對應的struct未實作Scanner則查詢時會出現Scan error on column index 3, name "contact": unsupported Scan, storing driver.Value type []uint8 into type *main.Contact: system internal error錯誤。

main.go

package main

import (
    "database/sql/driver"
    "encoding/json"
    "fmt"
    "time"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/schema"
)

// employee.contact要轉換的型態
type Contact struct {
    Name  string
    Phone string
}
type Employee struct {
    ID        int64
    Name      string
    Age       int
    Contact   Contact // mapping to column `employee.contact`
    CreatedAt time.Time
}

// column type to field type
//
// 讀取時employee.contact(jsonb) -> Employee.Contact
func (c *Contact) Scan(value interface{}) error {
    bytes, ok := value.([]byte)
    if !ok {
        return fmt.Errorf("Failed to unmarshal JSONB value=%v", value)
    }
    err := json.Unmarshal(bytes, c)
    return err
}

// field type to column type
//
// 插入時Employee.Contact -> employee.contact(jsonb)
func (c Contact) Value() (driver.Value, error) {
    json.Marshal(c)
    return json.Marshal(c)
}

const (...)

func getGormDB() *gorm.DB {...}

func main() {
    db := getGormDB()

    newEmp := Employee{
        Name: "john",
        Age:  33,
        Contact: Contact{
            Name:  "mary",
            Phone: "0912345678",
        },
    }

    db.Create(&newEmp)

    emp := Employee{}
    db.Last(&emp)

    fmt.Println(emp) // {1 john 33 {mary 0912345678} 2022-11-29 18:44:54.114161 +0000 UTC}
}

github。

• Customize Data Types - Scanner / Valuer
• Golang 查詢PostgreSQL的jsonb欄位資料