postgres将jsonb[]更改为jsonb

Question

我使用postgres9.4，并且存在“病人”与jsonb[]类型有“接触”栏的关系，如何将jsonb[]类型转换为jsonb

以下是记录在案的。

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

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

我尝试了如下方法，contact4是带有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

如果只使用jsonb数组的第一个元素，那么问题就很简单：

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

否则，可以使用以下函数：

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

Answer 2

从9.5版本开始，为了保存数据并将数据保存在json中作为数组，这将更好地工作。

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 '[]'