Search code examples
jsonpostgresqljsonb

Transform JSON array to a JSON map

I have a postgresql table called datasource with jsonb column called config. It has the following structure:

{  
   "url":"some_url",
   "password":"some_password",
   "username":"some_username",
   "projectNames":[  
      "project_name_1",
      ...
      "project_name_N"
   ]
}

I would like to transform nested json array projectNames into a map and add a default value for each element from the array, so it would look like:

{  
   "url":"some_url",
   "password":"some_password",
   "username":"some_username",
   "projectNames":{  
      "project_name_1": "value",
      ...
      "project_name_N": "value"
   }
}

I have selected projectNames from the table using postgresql jsonb operator config#>'{projectNames}', but I have no idea how to perform transform operation.

I think, I should use something like jsonb_object_agg, but it converts all data into a single row.

I'm using PostgreSQL 9.6 version.

Solution

  • You need to first unnest the array, then build a new JSON document from that. Then you can put that back into the column. 

    update datasource
  set config = jsonb_set(config, '{projectNames}', t.map)
from (  
  select id, jsonb_object_agg(pn.n, 'value') as map
  from datasource, jsonb_array_elements_text(config -> 'projectNames') as pn (n)
  group by id
) t
where t.id = datasource.id;

    The above assumes that there is a primary (or at least unique) column named id. The inner select transforms the array into a map.

    Online example: http://rextester.com/GPP85654