Gorm Update jsonb's nested field via jsonb_set?

Your Question

Using postgresql, with a column of type jsonb. What's the best way to update jsonb's nested field via jsonb_set with gorm?

e.g:

-- the sql update `answers` jsonb field's nested property `multi.lOjNn9qfJgEGLBIxxe0ka`, which is an array.
update quiz_run set answers = jsonb_set(answers, '{multi,"lOjNn9qfJgEGLBIxxe0ka"}', '["B","D"]'::jsonb, true) where id = 1;

I've check https://gorm.io/docs/update.html#Update-with-SQL-Expression. My current solution is: 1. construct the whole jsonb_set(..) as a string by hand, which is pretty complex to do. 2. then pass above string to gorm.Expr(), to get a Expr, 3. then pass above Expr to Update().

It works, but I'm wondering is there a better solution? Thanks.

Comment From: uded

A simple solution is to use gorm.Expr, works for me just fine:

db.Model(&Model{}).Where("id", id).Update("jsonb_data", gorm.Expr("jsonb_set(field, '{select,"+selector+"}', ?)", dataToUpdate)

I've tried using param as a selector, but SQL casting (wrapping string with quotes, etc.) was not helping.

Comment From: a631807682

It works, but I'm wondering is there a better solution? Thanks.

implements clause.Expression interface refer to https://github.com/go-gorm/datatypes/blob/master/json.go#L289

Comment From: uded

As long as it's MySQL or SQLite. PostgresSQL - not good support, need a PR there.