Updating JSON document in place

General
#1

We are currently working on optimizing our queries (we just moved off Mongo into Cockroach).
One of the heavy hitters we’d like to get rid off involves updating a property of a JSON object.
We currently load the document, update the value and write it back.
We’d like to do that in the database without having to load the whole thing into the client.

I took a look at json_set, but requires me to provide property-key and the value to then do an update.
I need to provide something like {“a”:“the_value_I_dont_know”} where I really just want to provide “a”.

Is there a way to do this?

How to update few fields of jsonb
#2

I don’t quite understand your question. Does this work? It uses json_set to set a specific field in a json column without returning the data to the client.

create table t (j json);
insert into t values ('{"a":123,"b":456}');
update t set j = json_set(j, '{a}'::string[], '789') where true;
select * from t;

           j            
+----------------------+
  {"a": 789, "b": 456}
#3

I am sorry, was mislead by the examples. Your example works as expected.

Thanks for sharing.

#4

What if we want to update JSONB column for a row that already has data:

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)

How do we do that?