Update a value for a key in JSONB object which has an array of arrays

Hi, i was wondering if we can update value of multiple keys inside an array in a JSONB object for all the rows in a table. Please find an example below


create table course_enrollement( enrollment_id INT PRIMARY KEY DEFAULT unique_rowid(),user_id INT UNIQUE, course_enrollment_details JSONB);
insert into course_enrollement( enrollment_id ,user_id , course_enrollment_details) values (1, 1001,'{"courses": [{"code": 1001,"state": "enrolled","agreements": [{"uuid": "","code": 231,"version": "v1"},{"uuid": "","code": 232,"version": "v2"}]},{"code": 1002,"state": "pending confirmation","agreements": [{"uuid": "","code": 231,"version": "v1"},{"uuid": "","code": 232,"version": "v2"}]}]}');

insert into course_enrollement( enrollment_id ,user_id , course_enrollment_details) values (2, 1002,'{"courses": [{"code": 1001,"state": "enrolled","agreements": [{"uuid": "","code": 231,"version": "v1"},{"uuid": "","code": 232,"version": "v2"}]},{"code": 1002,"state": "pending confirmation","agreements": [{"uuid": "","code": 231,"version": "v1"},{"uuid": "","code": 232,"version": "v2"}]}]}');

How can i jsonb_set the “uuid” field for each of the entries. I was looking to update the “uuid” with gen_random_uuid() function.

I wasnt able to figure out how to use jsonb_set for setting values of elements inside array of array

Although CRDB doesn’t currently have docs for this, you should be able to figure this out by looking at the Postgres docs for jsonb_set, since CRDB is Postgres-compatible.

For future reference, this is a continuation of a question from StackOverflow.

You can find the docs on cockroach json/jsonb functions here: https://www.cockroachlabs.com/docs/v21.1/functions-and-operators.html#jsonb-functions.

To clarify, the thing for which CRDB currently lacks documentation is the path argument of jsonb_set, or how to work with a JSON “path” in general. There are two helpful examples in the Postgres docs:

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false) → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]') → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]