Search code examples
postgresqljsonb

How do I modify jsonb in place, adding keys and moving values to aid querying later

I've got the following hypothetical data items in my postgres db, table: user_tracking

id: 210 
date: 2022-02-06 17:05:44.000 +0600 
item_json: {"MouseDown": "202,2525"}

id: 212 
date: 2022-02-06 17:05:46.000 +0600 
item_json: {"MouseUp": "299,2530"}

The problem is that I've made a mistake with how I've put the data in the jsonb, what I actually want is:

id: 210 
date: 2022-02-06 17:05:44.000 +0600 
item_json: {"action": "MouseDown", "value": "202,2525"}

id: 212 
date: 2022-02-06 17:05:46.000 +0600 
item_json: {"action": "MouseUp", "value": "299,2530"}

I'm trying to work out how to modify the data that is there, row by row, to get the result as I realised that this isn't an optimal queryable format otherwise.

Solution

  • You can deconstruct the JSONB object into a table using jsonb_each, and construct a new one using jsonb_build_object. (Documentation)

    UPDATE user_tracking SET item_json = (
  SELECT jsonb_build_object(
    'action', key,
    'value', value
  )
  FROM jsonb_each(item_json)
);

    (Make sure you back up your table before any big changes like this, just in case.)