Upsert Postgres JSONB column using GORM

Question

There is a table in Postgres

Id	Code	Data (jsonb)
1	xc1	{"name": "xx", "instance":"test_instance", "system": "test_system"}
2	cc3	{"name": "yy", "instance":"prod_instance", "system": "prod_system"}

I need to upsert Code column and fields 'instance' and 'system' from the column Data jsonb.

I have the upsert like this:

db *gorm.DB
....

db.Clauses(clause.OnConflict{
        Columns:   []clause.Column{{Name: "Id"}},
        DoUpdates: clause.AssignmentColumns([]string{"Code", "Data"}),
    }).CreateInBatches(&projects, 3000).Error

In this case the whole jsonb is updated (including 'name'). Is there any way to pass to AssignmentColumns which fields from the jsonb must be updated.

Answer 1

After deep research it is found out that PostgreSQL and GORM does not directly support updating a specific key in a JSONB column in an ON CONFLICT DO UPDATE clause.

The solution would typically need to fetch the existing data, merge it with the new data in Go, and then insert it again.

However, we can try a different approach using CreateInBatches. We separate the process into two steps: first insert new projects if they do not exist, and then, in a second separate step, update existing ones.