skip to Main Content

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


  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.

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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search