CODEWITHSUNDEEP
gojsonbcockroachdb
cpuNram
cpuNram

Reputation: 205

how to update few fields of jsonb in golang cockroachdb

I have table in cockroachdb as below:

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

Now I want to update only two keys of the jsonb - degree and university. How do I do that?

I have a solution but that requires all the four fields needs to be supplied.

Here is what I have in 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)

Working solution based on @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)

Upvotes: 2

Views: 1427

Answers (1)

Bergi
Bergi

Reputation: 664503

To update individual properties of a jsonb object, use jsonb_set:

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

You can nest the calls to do multiple updates:

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

Alternatively, you can also simply use the || operator to merge new values into the object:

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

Upvotes: 3

Related Questions