postgresql - 如何将元素添加到 Postgres 中的 json 字段数组

Question

我正在尝试将数据附加到属于 postgres 中的 json 字段的数组。在使用 pgAdmin 时，我知道以下查询有效。 ~

UPDATE lesson SET data =
    jsonb_set (data, '{pages, 999999}', '{"pageNum": 2, "pageType": "voc"}', True)
WHERE id = 2;

我只是想通过我用 go 编写的 rest api 使上述查询工作。我收到一条错误消息，内容为“pq:json 类型的输入语法无效”。

我的代码如下~

_, err := db.Exec(`
    UPDATE lessons SET data =
        jsonb_set (data, '{pages, 999999}','{"pageNum": $1, "pageType": $2}', True) 
    WHERE id = $3`,
    pageNum, pageType, id) // variable types are int string int

我怀疑 postgres 驱动程序没有插入 $ 参数。如果我对整个查询使用 fmt.Sprinf() 它将起作用，但我试图避免 SQL 注入(inject)攻击，并且想利用 go sql 库的内置安全措施。

引用我的数据结构如下~课表

Lessons
   id    int
   data  jsonb

Go 结构:

type Lesson struct {
    ID    int    `json:"id"`
    Name  string  `json:"name"`
    Pages []Page  `json:"pages"`
}

type Page struct {
    PageNum    int  `json:"pageNum"`
    PageType   string `json:"pageType"`

Answer 1

您不能在 Postgres 的字符串中使用查询参数。将整个字符串作为单个参数传递给 Postgres:

str := fmt.Sprintf('{"pageNum": %d, "pageType": %q}', pageNum, pageType)
_, err := db.Exec(`
    UPDATE lessons SET data =
        jsonb_set (data, '{pages, 999999}', $1, True) 
    WHERE id = $2`,
    str, id) // variable types are int string int

或使用 string concatenation在服务器端执行:

_, err := db.Exec(`
    UPDATE lessons SET data =
        jsonb_set (data, '{pages, 999999}','{"pageNum": ' || $1 || ', "pageType": ' || $2 || '}', True) 
    WHERE id = $3`,
    pageNum, pageType, id) // variable types are int string int

最好/最安全的可能是第一种方法，在您的客户端中使用完整的 JSON 编码(marshal)处理，而不是简单的 fmt.Sprintf。我将其作为练习留给读者。