Can I change a JSONB key with a query?

Question

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?

Answer 1

You have to update the current content, using the magic - and the jsonb function jsonb_insert();

CREATE TABLE foo(j jsonb);

INSERT INTO foo(j) VALUES ('{
  "kws": {},
  "distinct": 0,
  "total_count": 0
}'::jsonb)
RETURNING *;

UPDATE foo
SET j = (jsonb_insert(j, '{counter}', j->'total_count')) - 'total_count'
RETURNING *;

The new key will be named counter and is using the old value for total_count.

Answer 2

Use ? path-exists operator to narrow down the update, the jsonb_set() accordingly, with create_if_missing parameter set to true, then remove the unwanted key with a -: demo

update my_table
set jbdata = jsonb_set(jbdata,
                       '{count}',
                       coalesce(jbdata->'count',jbdata->'total_count'),
                       true)-'total_count'
where jbdata ? 'total_count'

You can speed things up by combining that with a jsonb_ops GIN index:

create index jsonb_ops_idx on my_table using gin(jbdata jsonb_ops);

Answer 3

Another approach (than the jsonb_insert + - shown by @FrankHeikens) is to use a subquery that expands all key-value pairs (using jsonb_each) and re-aggregates them (using jsonb_object_agg) while literally re-keying them:

UPDATE example
SET foo = (
  SELECT jsonb_object_agg(CASE key WHEN 'total_count' THEN 'counter' ELSE key END, value)
  FROM jsonb_each(foo)
)
WHERE foo ? 'total_count';

^{(online demo)}