duanhegn231318 2019-03-29 07:27
浏览 1109
已采纳

如何在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"`
  • 写回答

1条回答 默认 最新

  • doulao1934 2019-03-29 07:36
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 C++ /QT 内存权限的判断函数列举
  • ¥15 深度学习GFnet理解问题
  • ¥15 单细胞小提琴堆叠图代码
  • ¥80 LS dyna mpp并行报错
  • ¥50 升级strust2版本到2.3.15.1后使用ognl3.0.6.jar windows环境中没有问题,但部署到linux环境报错
  • ¥15 vue页面,node封装接口
  • ¥15 求TMS320F280039C工程模板!
  • ¥15 delphi+fastreport实现分组补空打印问题
  • ¥15 使用python把两台mysql数据库服务器数据导出和导入
  • ¥15 NodeBB论坛配置Apache Solr中文搜索引擎的详细教程