CODEWITHSUNDEEP
jsonpostgresqljsonb
DieZZzz
DieZZzz

Reputation: 135

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.

Upvotes: 0

Views: 3004

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51511

are you looking for smth like:

t=# with c(j) as (values('{
   "url":"some_url",
   "password":"some_password",
   "username":"some_username",
   "projectNames":[
      "project_name_1",
      "project_name_N"
   ]
}
'::jsonb))
, n as (select j,jsonb_array_elements_text(j->'projectNames') a from c)
select jsonb_pretty(jsonb_set(j,'{projectNames}',jsonb_object_agg(a,'value'))) from n group by j
;
            jsonb_pretty
------------------------------------
 {                                 +
     "url": "some_url",            +
     "password": "some_password",  +
     "username": "some_username",  +
     "projectNames": {             +
         "project_name_1": "value",+
         "project_name_N": "value" +
     }                             +
 }
(1 row)

Time: 19.756 ms

if so, look at:

https://www.postgresql.org/docs/current/static/functions-aggregate.html

https://www.postgresql.org/docs/current/static/functions-json.html

Upvotes: 0

Related Questions