PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can I change a JSONB key with a query?

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?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
  3. 0

    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)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search