postgresql - postgres change jsonb[] to jsonb

Question

I use postgres9.4, and there exists relation "Patients" has column "contact" with type jsonb[], how to transfer type jsonb[] to jsonb?

The following is on record.

=>select name, contact from "Patients" where contact is not null;

name  |                                               contact                                               
--------+-----------------------------------------------------------------------------------------------------
"tom" | {"{"name": "tom", "phone": "111111", "address": "shanghai", "relation": "your_relation"}"}

I have tried as the followings, contact4 is column with type jsonb

alter table "Patients" alter column contact4 type jsonb using contact4::text::jsonb;

ERROR:  invalid input syntax for type json
DETAIL:  Expected ":", but found "}".
CONTEXT:  JSON data, line 1: ...ress": "shanghai", "relation": "your_relation"}"}

Answer 1

If only the first element of jsonb array is used then the issue is simple:

alter table "Patients" alter column contact type jsonb using contact[1]::jsonb;

else you can use the following function:

create or replace function jsonb_array_to_jsonb(jsonb[])
returns jsonb language sql as $$
    select jsonb_object_agg(key, value)
    from unnest($1), jsonb_each(unnest)
$$;

alter table "Patients" alter column contact type jsonb using jsonb_array_to_jsonb(contact);