go - 如何在golang cockroachdb中更新jsonb的几个字段

Question

我在cockroachdb中有以下表格:

         key        |                          value                           
+-------------------+---------------------------------------------------------+
  <host_ipa>        | {“student_name": “John", “degree": “Bachelors”, “university”: “KU”, “Company”: “ABC"}  

现在，我只想更新 jsonb- degree和 university的两个键。我怎么做？

我有一个解决方案，但是这需要提供所有四个字段。

这是我在golang中拥有的东西:

sqlCmd := fmt.Sprintf(" UPDATE %v set value = json_object('{%v, %v, %v, %v}'::string[], '{%v, %v, %v, %v}'::string[]) WHERE key = '%v' ",
        myTable,
        strconv.Quote("student_name”),
        strconv.Quote("degree"),
        strconv.Quote(“university”),
        strconv.Quote("Company"),
        strconv.Quote(student),
        strconv.Quote(newDegree),
        strconv.Quote(newUniversity),
        strconv.Quote(company),
        host)
    db, err := sql.Open(dbDriver, DBConnStr)
    _, err = db.Exec(sqlCmd)

基于@Bergi的工作解决方案:

    db, err := sql.Open(dbDriver, DBConnStr)    
    sqlCmd := "UPDATE " + myTable + " SET value = value || jsonb_build_object(‘degree', $1::string, ‘university', $2::string) WHERE key = $3"
    _, err = db.Exec(sqlCmd, newDegree, newUniversity, host)

Answer 1

要更新jsonb对象的各个属性，请使用 jsonb_set :

UPDATE myTable SET value = jsonb_set(value, '{degree}', $1::text) WHERE key = $2

您可以嵌套调用以进行多个更新:

UPDATE myTable SET value = jsonb_set(jsonb_set(value, '{degree}', $1::text), '{university}', $2::text) WHERE key = $3

另外，您也可以简单地使用 the || operator将新值合并到对象中:

UPDATE myTable SET value = value || jsonb_build_object('degree', $1::text, 'university', $2::text) WHERE key = $3