I have a JSONB column in a postgres database. The json object it holds is structured like this:
{
"kws": {},
"distinct": 0,
"count": 0
}
Some of the records have nonzero/non-null values, of course.
A colleague has inserted quite a few records where he made the key total_count instead of count. Is there a way to change that key with a query rather than retrieving each record and updating the field?
3
Answers
You have to update the current content, using the magic
-
and the jsonb function jsonb_insert();The new key will be named
counter
and is using the old value for total_count.Use
?
path-exists operator to narrow down the update, thejsonb_set()
accordingly, withcreate_if_missing
parameter set totrue
, then remove the unwanted key with a-
: demoYou can speed things up by combining that with a
jsonb_ops
GIN index:Another approach (than the
jsonb_insert
+-
shown by @FrankHeikens) is to use a subquery that expands all key-value pairs (usingjsonb_each
) and re-aggregates them (usingjsonb_object_agg
) while literally re-keying them:(online demo)