Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - postgres change jsonb[] to jsonb

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"}"}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...