因?yàn)槲覠o(wú)法發(fā)表評(píng)論，所以我假設(shè)：
您正在使用 golang 的database/sql或類似的包。
在您的數(shù)據(jù)庫(kù)中，details列具有類型JSONB
一種簡(jiǎn)單的方法是循環(huán)切片layers并為此構(gòu)建查詢字符串：
"INSERT INTO layers (id,city,details) VALUES ($1,$2,$3), ($4,$5,$6)"
對(duì)于id和city，您可以輕松傳遞參數(shù)，但是您需要為 傳遞 JSON 字節(jié)details。這意味著，您需要將詳細(xì)信息結(jié)構(gòu)編組為 JSON 字節(jié)以進(jìn)行插入/更新，并在 SELECT 時(shí)將“詳細(xì)信息”結(jié)果解組為結(jié)構(gòu)
您將需要：
定義封裝切片的新結(jié)構(gòu)Detail（我們稱之為Details）然后Details應(yīng)該實(shí)現(xiàn)這些接口。
實(shí)現(xiàn)driver.Valuer接口轉(zhuǎn)換Details為數(shù)據(jù)庫(kù)可以理解的JSON字節(jié)切片
實(shí)現(xiàn)sql.Scanner接口以將 JSON 字節(jié)切片從數(shù)據(jù)庫(kù)解組到您的結(jié)構(gòu)
代碼應(yīng)如下所示：
type Detail struct {
Total int `json:"total"`
Gender string `json:"gender"`
}
// this will implement driver.Valuer and sql.Scanner
type Details []Detail
// so that the database can understand your value, useful for INSERT/UPDATE
func (d Details) Value() (driver.Value, error) {
return json.Marshal(d)
}
// so that the database can convert db value to your struct, useful for SELECT
func (d *Details) Scan(value interface{}) error {
b, ok := value.([]byte)
if !ok {
return errors.New("type assertion to []byte failed for scanning Details")
}
return json.Unmarshal(b, &d)
}
完整代碼：
package main
import (
"database/sql"
"database/sql/driver"
"encoding/json"
"errors"
"fmt"
"log"
"strings"
_ "github.com/lib/pq"
)
type Layer struct {
ID int `json:"id"`
City string `json:"city"`
Details Details `json:"details"`
}
// this will implement driver.Valuer and sql.Scanner
type Details []Detail
// so that the database can understand your value, useful for INSERT/UPDATE
func (d Details) Value() (driver.Value, error) {
return json.Marshal(d)
}
// so that the database can convert db value to your struct, useful for SELECT
func (d *Details) Scan(value interface{}) error {
b, ok := value.([]byte)
if !ok {
return errors.New("type assertion to []byte failed for scanning Details")
}
return json.Unmarshal(b, &d)
}
type Detail struct {
Total int `json:"total"`
Gender string `json:"gender"`
}
func main() {
db, err := sql.Open("postgres", "postgres://user:pass@host:port/db?sslmode=disable")
exitIfError(err)
query, params := prepareQuery([]Layer{
{
ID: 107509018555,
City: "London",
Details: []Detail{{Total: 158, Gender: "Male"}, {Total: 689, Gender: "Female"}},
},
{
ID: 108509018556,
City: "New York",
Details: []Detail{{Total: 756, Gender: "Male"}, {Total: 356, Gender: "Female"}},
},
})
log.Println(query)
// INSERT INTO layers (id, city, details) VALUES ($1, $2, $3),($4, $5, $6)
log.Println(params)
// [107509018555 London [{158 Male} {689 Female}] 108509018556 New York [{756 Male} {356 Female}]]
result, err := db.Exec(query, params...)
exitIfError(err)
rows, _ := result.RowsAffected()
log.Println(rows) // 2 rows inserted
}
func exitIfError(err error) {
if err != nil {
log.Fatal(err)
}
}
func prepareQuery(layers []Layer) (string, []interface{}) {
query := "INSERT INTO layers (id, city, details) VALUES "
params := []interface{}{}
x := 1
for _, layer := range layers {
query += fmt.Sprintf("($%d, $%d, $%d),", x, x+1, x+2)
params = append(params, layer.ID, layer.City, layer.Details)
x += 3
}
query = strings.TrimSuffix(query, ",")
return query, params
}
