[解決済み] 不正な配列リテラル - PostgreSQL

質問

jsonbのフィールドからPostgreSQLの配列カラムに配列をコピーしたいのですが、どうすればよいですか？

CREATE TABLE survey_results (
    id integer NOT NULL,
    areas text[],  
    raw jsonb DEFAULT '{}'::jsonb
);

INSERT INTO survey_results (id, raw)
    VALUES (1, '{"areas": ["test", "test2"]}');

UPDATE survey_results SET areas = CAST(raw#>>'{areas}' AS text[]);

これは私を返すのか？

ERROR: malformed array literal: "["test", "test2"]" Detail: "[" must introduce explicitly-specified array dimensions.

どうすれば直るのでしょうか？

http://sqlfiddle.com/#!17/d8122/2

解決方法は？

http://sqlfiddle.com/#!17/d8122/33

json配列はpostgresの配列にセルフキャストできません。適切にパースしてキャストする必要があります ( json_array_elements , unnest , array_agg のように）、あるいはモンキーハックを使う。

UPDATE survey_results 
SET areas = concat('{',translate(raw#>>'{areas}','"',$$'$$),'}')::text[];

上記で、json 配列を準備し、識別子ではなくリテラルになるように引用符を変更し、postgres の配列のテキスト表現に準備しました。 '{}'

のような代替案があります。

with a as (
  select jsonb_array_elements_text(raw#>'{areas}') e from survey_results 
)
, b as (
select array_agg(e) ag from a
)
UPDATE survey_results 
SET areas = ag::text[]
FROM b;
select * from survey_results
  ;

を使用することができます - より安全なキャスティングのためです。