如何在Postgres中将元素添加到JSON字段数组

问题描述：

I'm trying to append data to an array that belongs to a json field in postgres. While using pgAdmin I know the following query works. ~

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

I am simply trying to get the above query to work via my rest api written in go. I am getting an error that reads "pq: invalid input syntax for type json".

my code is as follows~

_, 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

I suspect that the postgres driver isn't interpolating the the $ parameters. It will work if I use fmt.Sprinf() for the whole query but I am trying to avoid SQL injection attacks, and would like to take advantage of the built in security measures of the go sql library.

For reference my data is structured as follows~ Lessons Table

Lessons
   id    int
   data  jsonb

Go structs:

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"`

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

  UPDATE课程SET data = 
 jsonb_set（数据，'{pages，999999}'，'{“ pageNum”：2，2，“ pageType”：“ voc”}  '，True）
WHERE id = 2; 
  code>  pre> 
 
 我只是想通过使用go编写的rest api来使以上查询正常工作。 我收到一条错误消息，内容为“ pq：json类型的无效输入语法”。 p> 
 
 
我的代码如下〜 p> 
 
 
  _  ，err：= db.Exec（`
更新课程SET data = 
 jsonb_set（数据，'{pages，999999}'，'{“ pageNum”：$ 1，“ pageType”：$ 2}'，True）
  WHERE id = $ 3`，
 pageNum，pageType，id）//变量类型为int字符串int 
  code>  pre> 
 
 我怀疑postgres驱动程序未对 $参数。 如果我在整个查询中都使用fmt.Sprinf（），它将起作用，但是我试图避免SQL注入攻击，并且想利用go sql库的内置安全措施。 p> 
 \  n 
供参考，我的数据结构如下〜
课程表 p> 
 
 
 课程
 id int 
数据jsonb 
  code>  pre> \  n 
 执行结构： p> 
 
 
  type课程结构{
 ID int`json：“ id”`
名称字符串`json：“ name”`\  n Pages [] Page`json：“ pages”`
} 
 
type页面结构{
 PageNum int`json：“ pageNum”`
 PageType字符串`json：“ pageType”`
  code>   pre> 
  div>

答

You cannot use query parameters within a string in Postgres. Either pass the entire string to Postgres as single parameter:

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

or use string concatenation to do it on the server side:

_, 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

The best/safest, is probably the first approach, with full JSON marshaling in your client, rather than a simple fmt.Sprintf. I leave that as an exercise for the reader.

如何在Postgres中将元素添加到JSON字段数组

相关推荐