0

Array (name, value) to map.

[{'name':'email,'value':'[email protected]'},{'name':'phone','value':123123}]

to

{'email':'[email protected]','phone':123123}

I need to organize search and sorting such email and I would like to simplify to make View my Table with map data struct.

happy_yar
  • 61
  • 1
  • 4
  • Possible duplicate of [How do I modify fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) – McNets Jan 19 '17 at 11:58

2 Answers2

0

I changed your input data to actually be json (dbl quotes instead on single and added keyname for array. After this transofrmation here is example of how to "build" it:

t=# with j as (
  select '{"arr":[{"name":"email","value":"[email protected]"},{"name":"phone","value":123123}]}'::json v
)
select
  concat('{',v->'arr'->0->'name',':',v->'arr'->0->'value',',',v->'arr'->1->'name',':',v->'arr'->1->'value','}')::json
from j
;
 {"email":"[email protected]","phone":123123}
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
0
with t(id,j) as (
  values(1,'[{"name":"email","value":"[email protected]"},{"name":"phone","value":123123}]'::json))
select json_object_agg(a.j->>'name', a.j->>'value')
from t, json_array_elements(j) a(j) group by id;
╔════════════════════════════════════════════════════╗
║                  json_object_agg                   ║
╠════════════════════════════════════════════════════╣
║ { "email" : "[email protected]", "phone" : "123123" } ║
╚════════════════════════════════════════════════════╝
Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • Thanks! this works.`select json_object_agg(a->>'name',a->>'value') from json_array_elements('[{"name":"email","value":"[email protected]"}, {"name":"phone","value":"123123"},{"name":"home","value":1}]') as a` – happy_yar Jan 19 '17 at 12:37
  • @happy_yar Yes it is simpler a bit and you can use it as a subquery in the `select` clause or as a source for the `update` statement. – Abelisto Jan 19 '17 at 12:42