How to update few fields of jsonb

I have table in CRDB as below:

     key        |                          value                           

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

I would like to update only two keys of the jbonb - “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),
hostile)
db, err := sql.Open(dbDriver, DBConnStr)
_, err = db.Exec(sqlCmd)

Hey @israj,

You could use the json_set() function. Here’s an older forum post that gives you an example of how you use it Updating JSON document in place.

If you’re updating a string, make sure you have the string formatted like this for the new value '"string"' or you may get an error.

Thanks,

Thanks @ronarev. I tried below, but it fails.

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

error - pq: unknown signature: jsonb_build_object(string, placeholder{1}, string, placeholder{2})

Hey @lsraj,

Something like this should work for you:

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

func main() {
	pgConn, err := sql.Open("postgres", "postgres://root@localhost:26257/defaultdb?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	someInsert := map[string]int{"apple": 5, "lettuce": 7}
	jsonValues, _ := json.Marshal(someInsert)

	jsonInsert, err := pgConn.Exec("INSERT INTO t1 (id) VALUES ($1);", jsonValues)
	if err != nil {
		log.Fatal(err)
	}
	jsonAffected, err := jsonInsert.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("ROWS AFFECTED: %d\n", jsonAffected)

	q := `
	update t1 set id = json_set(id, '{apple}'::string[], '9') where true;
`
	//fmt.Printf(q)
	row := pgConn.QueryRow(q)
	row.Scan(someInsert)

}```

Thanks!

Hi @ronarev,

Thanks for your time and example.

What I was looking for is parameterized db.Exec() to avoid SQL injection attacks. Also, among ‘N’ number of jsonb properties, I would be updating minimum of two properties, so, instead of json_set(),
I think, jsonb_object_build() seems good choice. When I build the string with fmt.Sprintf(), it works. So
the below code is working:

    sqlCmd := fmt.Sprintf("UPDATE  %v SET value = value || jsonb_build_object('degree', '%v', 'university', '%v') WHERE key = '%v'",
        myTable, newDegree, newUniversity, host) 
    db, err := sql.Open(dbDriver, DBConnStr)
    _, err = db.Exec(sqlCmd) 

But the parameterized args code fails:

    newDegreeQuoted := fmt.Sprintf("'%v'", newDegree)
    newUniversityQuoted := fmt.Sprintf("'%v'", newUniversity)
    hostQuoted := fmt.Sprintf("'%v'", host) 

    // newDegreeQuoted := fmt.Sprintf("'%v'", pq.QuoteIdentifier(newDegree))
    // newUniversityQuoted := fmt.Sprintf("'%v'", pq.QuoteIdentifier(newUniversity))
    // hostQuoted := fmt.Sprintf("'%v'", pq.QuoteIdentifier(host))

    sqlCmd := "UPDATE " + s3KeysTable + " SET value = value || jsonb_build_object('access_key', $1, 'secret_key', $2) WHERE key = $3"
    _, err = db.Exec(sqlCmd, newDegreeQuoted, newUniversityQuoted, hostQuoted) 
    

The error is - pq: unknown signature: jsonb_build_object(string, placeholder{1}, string, placeholder{2}                                                            

I did enable CRDB SQL loggging on a node. And I could fix the issue now. The fix is below:

    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)

Lesson learned - jsonb properties need to have type specified, in my case it was $1::string and $2::string.

Hi @lsraj ,

Glad you were able to find a resolution!

Regards,

Ron