UPDATE statement documentation for JSONB

Hi,

What is the best way to update an empty JSONB field. It would be nice if JSONB documentation (https://www.cockroachlabs.com/docs/stable/jsonb.html) can be updated with some sample update scenarios for JSONB.

create table t (id int, j json);
insert into t (id) values (1);
update t set j = json_set(j, '{a}'::string[], '789') where id = 1;
select * from t;

id |  j    
+----+------+
1 | NULL  
(1 row)

Hi @batman

Are you trying to update the value to the JSON { "a": "789"}?

If so this worked for me:

update t set j = json_object('{"A", "789"}'::string[]) where id =1;

I’ve also created a docs issue to work on adding UPDATE examples to the JSONB docs.

Hi @rmloveland,

Ok, that worked. It also works if data already exists. Thanks for taking the time to answer + creating an issue for updated docs.

We are having a good time so far using crdb. Keep up the great work.

batman via Cockroach Labs cockroachlabs@discoursemail.com writes:

Hi @rmloveland,

Ok, that worked. It also works if data already exists. Thanks for
taking the time to answer + creating an issue for updated docs.

My pleasure! Hope to get those docs updated soon.

We are having a good time so far using crdb. Keep up the great work.

Thanks batman! Glad to hear you are enjoying CockroachDB.

@rmloveland Ok, so while that update statement works nicely on the sql client, typically most queries will be run via an app. Hence parametrizing the input is important.

The only way I can think of doing it via code is like this:

Example Golang code:

query := `update t set j = json_object(’{“A”, “$2”}’::string[]) where id =$1

But that gives the following error: pq: got 2 parameters but the statement requires 1.

If the documentation could also include parameterization for json updates, that would be great.

Thanks.

Hi @batman,

You can use SQL parameters to construct a JSON object using the json_build_object function, so I think something like this:

update t set j = json_build_object('A', $2::string) where id = $1

should do what you want.

Hi @justin, thanks a lot - that worked.