Go 查詢PostgreSQL的jsonb型態欄位的JSON資料範例。
範例環境：
- Go 1.17
- PostgreSQL 14
PostgreSQL的postgres資料庫的employee資料表有一筆資料如下。employee.contact的型態為jsonb。
id | name | age | contact | created_at
----+------+-----+-------------------------------------------+----------------------------
1 | john | 33 | { "name": "mary", "phone": "0912345678" } | 2022-02-15 21:12:26.308245
下面程式連線到PostgresSQL資料庫查詢employee資料表並將contact欄位的JSON binary資料依序轉為string、map、及Contact struct物件。
main.go
package main
import (
"database/sql"
"encoding/json"
"fmt"
"time"
_ "github.com/lib/pq"
)
type Employee struct {
ID int64
Name string
Age int
Contact []byte
CreatedAt time.Time
}
type Contact struct {
Name string
Phone string
}
const (
HOST = "localhost"
PORT = "5432"
DATABASE = "postgres"
USER = "admin"
PASSWORD = "12345"
SSL = "disable"
)
func connect() *sql.DB {
driver := "postgres"
dsn := fmt.Sprintf(
"host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
HOST, PORT, USER, PASSWORD, DATABASE, SSL)
db, err := sql.Open(driver, dsn)
if err != nil {
panic("open database error")
}
return db
}
func main() {
db := connect()
emp, err := GetEmployeeByID(db, 1)
if err != nil {
panic(err)
}
s := string(emp.Contact)
fmt.Println(s) // {"name": "mary", "phone": "0912345678"}
var m map[string]interface{}
json.Unmarshal(emp.Contact, &m)
fmt.Println(m) // map[name:mary phone:0912345678]
var c Contact
json.Unmarshal(emp.Contact, &c)
fmt.Println(c) // {mary 0912345678}
}
func GetEmployeeByID(db *sql.DB, id int64) (*Employee, error) {
row := db.QueryRow("SELECT * FROM employee WHERE id = $1 LIMIT 1", id)
var emp Employee
err := row.Scan(
&emp.ID,
&emp.Name,
&emp.Age,
&emp.Contact,
&emp.CreatedAt,
)
if err != nil {
return nil, err
}
return &emp, nil
}
執行程式印出以下。
{"name": "mary", "phone": "0912345678"}
map[name:mary phone:0912345678]
{mary 0912345678}
沒有留言:
張貼留言