Transform JSON array to a JSON map

Question

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.

@a_horse_with_no_name It's just a string that will be default to all elements. — DieZZzz
– DieZZzz, Commented Mar 5, 2018 at 12:20

user330315 · Accepted Answer · 2018-03-05 12:36:43Z

1

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

answered Mar 5, 2018 at 12:36

user330315

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2018-03-05 12:34:35Z

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

Collectives™ on Stack Overflow

Transform JSON array to a JSON map

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related