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\"}"}

try ... using contact4[1]

user330315
– user330315

2016-05-13 11:21:35 +00:00
Commented May 13, 2016 at 11:21 — user330315
– user330315, Commented May 13, 2016 at 11:21

Ankur Agarwal · Accepted Answer · 2017-07-21 07:37:10Z

5

As of 9.5 version, to preserve the data and keep the data in json as array this would work much better.

ALTER TABLE "Patients"  ALTER COLUMN "contact" DROP DEFAULT
ALTER TABLE "Patients"  ALTER COLUMN "contact" TYPE jsonb USING to_json(contact)
ALTER TABLE "Patients"  ALTER COLUMN "contact" SET DEFAULT '[]'

answered Jul 21, 2017 at 7:37

Ankur Agarwal

8479 silver badges23 bronze badges

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

1 Comment

klin Over a year ago

Good point, to_json() or to_jsonb() preserves the structure of array.

klin · Accepted Answer · 2016-05-13 12:10:54Z

3

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

answered May 13, 2016 at 12:10

klin

123k15 gold badges241 silver badges263 bronze badges

Collectives™ on Stack Overflow

postgres change jsonb[] to jsonb

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related