網頁

▼

2022/2/15

Golang 查詢PostgreSQL的jsonb欄位資料

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}

沒有留言:

張貼留言