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}
}
沒有留言:
張貼留言