將可選 JSONB 欄位轉換為文本數組？

在my_table中，該misc列jsonb具有預設值{}。有些行具有misc.names文本值數組；其他行沒有misc.names鍵。

我想將該數據複製到單獨的names::text[]列，使用如下命令：

UPDATE my_table SET names = COALESCE(misc->>'names', ARRAY[]::text[])

換句話說，“如果該行有一個文本值列表misc->>'names'，則將其放入names列中，但如果misc->>'names'在NULL該行中，則將一個空文本數組放入names列中。”

但是，這失敗了：

ERROR: 42804: COALESCE types text and text[] cannot be matched

我不明白錯誤在說什麼。我究竟做錯了什麼？

misc->>'names'，雖然看起來像一個數組，但實際上text不是text[]。您需要將 jsonb 數組轉換為 postgresql 文本數組。在這個DB-Fiddle中探索了數據類型和執行轉換的方法，展示了一個簡單的string_to_array轉換可能不是我們想要的，而不是從呼叫的結果建構一個數組jsonb_array_elements_text。

CREATE TABLE my_table
(
   id          SERIAL,
   misc        jsonb DEFAULT '{}',
   expected    text[],
   names       text[]
);

INSERT  INTO my_table ( misc, expected )
VALUES  ( '{ "names": [ "A", "B", "C" ] }', ARRAY[ 'A', 'B', 'C' ] ),
       ( DEFAULT, ARRAY[]::text[] );
       
SELECT  misc, expected, names,
       misc->>'names',
       pg_typeof( misc->>'names' ),
       string_to_array( misc->>'names', ',' ),
       pg_typeof( string_to_array( misc->>'names', ',' ) ),
       ARRAY( SELECT jsonb_array_elements_text( misc->'names' ) ),
       pg_typeof( ARRAY( SELECT jsonb_array_elements_text( misc->'names' ) ) )
FROM    my_table;

UPDATE  my_table
   SET names = COALESCE( ARRAY( SELECT jsonb_array_elements_text( misc->'names' ) ), ARRAY[]::text[] );
   
SELECT  misc, expected, names
FROM    my_table;

假設misc->'names'是一個實際的 JSON 數組（不是“文本值列表”），我建議使用這個自定義翻譯函式：

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
 RETURNS text[]
 LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

詳細解釋：

• 如何將 JSON 數組轉換為 Postgres 數組？

那麼您的UPDATE查詢可以是：

UPDATE my_table
SET    names = COALESCE(jsonb_array_to_text_array(misc->'names'), '{}')
WHERE  names <> COALESCE(jsonb_array_to_text_array(misc->'names'), '{}');

請注意添加的（可選）WHERE子句以跳過不會更改任何內容的空更新（以全部成本）。這是假設names已定義NOT NULL。看：

• 我如何（或我可以）在多列上選擇 DISTINCT？

引用自：https://dba.stackexchange.com/questions/308814