What is the ideal way to update multiple fields within a jsonb column?
For example, if I had the data set
'
{
"field1": true,
"field2": 2,
"field3": "Something",
}
'
How would I update "field2" and "field3". Most questions on StackOverflow seem to be suggesting either a nested jsonb_set call or using the concat operator. Neither one of these solutions seems to be as straight forward as one would expect for updating more than one field in a jsonb column.
The jsonb_set function is canonical the one that updates an input jsonb value. However, it can only update a single element at a time. The concatenation operator doesn't update fields by itself by jsonb has the property of "last one stays" when faced with multiple instances of a given key. Thus if your data is amenable to leveraging that behavior, which your example data is, you can more compactly create the effects of an update by constructing a "update jsonb" and concatenating it against the original.
SELECT ($${
"field1": true,
"field2": 2,
"field3": "Something"
}$$::jsonb || $${
"field2": 3,
"field3": "Something Else"
}$$::jsonb)::text
If you have a thought for what would be a straight-forward way to multi-update it would be good to share. The json area is getting attention right now so feature requests have a decent chance of being added.