Change structure of the content of a JSON field in Postgres

Question

I have a table that contains a JSON column

CREATE TABLE mytable (id character varying(50),
                         data json NOT NULL)

into the field data I have a JSON array of object, let's say

[
  {
    "id": "id1",
    "name": "name1"
  },
  {
    "id": "id2",
    "name": "name2"
  }]

I want to enclose this data into a object like this and add an additional field too (another array of objects)

"parameters": [
  {
    "id": "id1",
    "name": "name1"
  },
  {
    "id": "id2",
    "name": "name2"
  }],
"settings": []

I have Postgres 15.

How can I resolve this issue?

I'm not very familiar with json field type, so I didn't tried anything yet

Answer 1

You can run an update and nest your whole current value inside a newly constructed one, under "parameters" key, using json_build_object():
_{demo at db<>fiddle}

update mytable 
set data=json_build_object( 'parameters',data --current value gets nested here
                           ,'settings',json_build_array()/*empty array*/)
returning *,jsonb_pretty(data::jsonb);

id	data	jsonb_pretty
id1	{"parameters" : [   {     "id": "id1",     "name": "name1"   },   {     "id": "id2",     "name": "name2"   }], "settings" : []}	{     "settings": [     ],     "parameters": [         {             "id": "id1",             "name": "name1"         },         {             "id": "id2",             "name": "name2"         }     ] }

It's worth pointing out that nothing stops that structure to vary between rows. The db won't prevent anyone from adding a row that holds some completely different structure - you'd have to set up check constraints or even constraint triggers to force all rows to stick to this format and reject anomalous entries. Might also be a good idea to reconsider normalization.

If you choose to stick with JSON types, consider jsonb - it's faster, lighter and way more flexible than plain json at the cost of (or with added benefits of) deflated insignificant whitespace, sorted and deduplicated keys. jsonb_pretty() formatter is one of many examples of features available for jsonb, but missing for json.

Unless you're using it for some specific cross-compatibility reasons, it might be a good idea to consider text instead of a varchar(x), especially if that 50-char limit is completely eyeballed.
Neither is recommended for a primary key, if that's what it is - integer and uuid types are preferred.