sql - 如何在 golang 应用程序中创建具有多个 JSON 参数的 SQL 查询？

Question

正如您在我的 中看到的那样戈朗 应用程序我有一个名为 layers 的数组.

type Details struct {
    Total int `json:"total"`
    Gender string `json:"gender"`
}

type Layer struct {
    ID int `json:"id"`
    City string `json:"city"`
    Details []Details `json:"details"`
}

layers := []Layer{
    {
        ID: 107509018555,
        City: "London",
        Details: []Details{
            {
                Total: 158,
                Gender: "Male",
            },
            {
                Total: 689,
                Gender: "Female",
            },
        },
    },
    {
        ID: 108509018556,
        City: "New York",
        Details: []Details{
            {
                Total: 756,
                Gender: "Male",
            },
            {
                Total: 356,
                Gender: "Female",
            },
        },
    },
}

我想将该数组的数据插入到 的表中PostgreSQL 数据库。我的问题是如何在应用程序中创建这样的 SQL 查询？

查询 :

INSERT INTO layers (ID, CITY, DETAILS) VALUES
(107509018555, 'London', '[{"total":158,"gender":"Male"},{"total":689,"gender":"Female"}]'::json),
(108509018556, 'New York', '[{"total":756,"gender":"Male"},{"total":356,"gender":"Female"}]':json);

Answer 1

因为我无法发表评论，所以我假设:

您正在使用 golang 的 database/sql或类似的包。

在您的数据库中，details列的类型为 JSONB

一个简单的方法是循环 slice layers并为此构建查询字符串:

"INSERT INTO layers (id,city,details) VALUES ($1,$2,$3), ($4,$5,$6)"

对于 id和 city ，您可以轻松传递参数，但是您需要为 details 传递 JSON 字节.这意味着，您需要 编码详细信息结构到 JSON 字节以用于插入/更新 和 SELECT 时将“详细信息”结果解码为结构

您将需要:

定义封装 Detail slice 的新结构(我们称之为 Details )然后是 Details应该实现这些接口(interface)。

实现 driver.Valuer转换接口(interface)Details转数据库可以理解的JSON字节 slice

实现 sql.Scanner将 JSON 字节 slice 从数据库解码到您的结构的接口(interface)

代码应如下所示:

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
}

引用:

https://www.alexedwards.net/blog/using-postgresql-jsonb

https://golang.org/pkg/database/sql/