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

我有一個data類型的列，json其中包含這樣的 JSON 文件：

{
   "name": "foo",
   "tags": ["foo", "bar"]
}

我想將嵌套tags數組轉換為串聯字元串 ( 'foo, bar')。這array_to_string()在理論上很容易實現。但是，此函式不接受json輸入。所以我想知道如何將這個 JSON 數組轉換為 Postgres 數組（類型text[]）？

Postgres 9.4 或更高版本

受這篇文章的啟發，Postgres 9.4 將缺失的函式添加到非嵌套 JSON 數組中。

感謝 Laurence Rowe 的更新檔和 Andrew Dunstan 的承諾！

• json_array_elements_text(json)
• jsonb_array_elements_text(jsonb)

使用array_agg()或ARRAY構造函式從. _ 或者用值列表（類型）建構一個字元串。text[]``text
string_agg()text

專注於數組輸出 ( text[])，而不是字元串 ( text)。重要區別：null元素保存在實際數組中。null這在不能包含值的字元串中是不可能的。真正的表示是一個數組。

將 ‘jsonb’ 替換為 ‘json’ 以輸入json以下所有 SQL 程式碼。

TLDR：使用自定義函式

將邏輯封裝在一個函式中以供重複使用：

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

稱呼：

SELECT tbl_id, jsonb_array_to_text_array(data->'tags')
FROM   tbl;

• **LANGUAGE sql**對於簡單的功能。（在我使用 Postgres 14 的最新測試中最快。）

• IMMUTABLE（因為它是）避免在更大的查詢中重複評估並允許在索引表達式中使用它。

• PARALLEL SAFE（在Postgres 9.6或更高版本中！）允許在大查詢中並行執行。看：

  • 何時將函式標記為 PARALLEL RESTRICTED 與 PARALLEL SAFE？

• **STRICT**返回null輸入null。另外：更快。由於 ARRAY 建構子，該函式無論如何都不能內聯STRICT，所以不能損害它。

這個帶有STRICT修飾符的函式也盡可能地忠實於原始函式，因為它null為null輸入返回，為空數組輸入返回一個空數組。比以下所有查詢都要好。

各種解決方案，一步一步

立即聚合或相關子查詢中的每行，然後保留****原始順序，我們不需要，甚至外部查詢中的唯一鍵。看：LATERAL``ORDER BY``GROUP BY

• 如何結合聚合函式應用 ORDER BY 和 LIMIT？
• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？
• 如何結合聚合函式應用 ORDER BY 和 LIMIT？
• 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？

基本查詢，返回null空數組或null輸入：

SELECT t.tbl_id, d.txt_arr
FROM   tbl t
CROSS  JOIN LATERAL (
  SELECT array_agg(d.elem) AS txt_arr
  FROM   jsonb_array_elements_text(t.data->'tags') AS d(elem)
  ) AS d;

簡短的語法，返回null空數組或null輸入：

SELECT t.tbl_id, d.txt_arr
FROM   tbl t, LATERAL (
  SELECT array_agg(value) AS txt_arr
  FROM   jsonb_array_elements_text(t.data->'tags')  -- default name is "value"
  ) d;

使用 ARRAY 建構子更短（更快），為空數組或null輸入返回空數組：

SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM   tbl t, LATERAL (
  SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))
  ) d(txt_arr);

使用相關子查詢甚至更短（更快），為空數組或null輸入返回空數組：

SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>在這裡擺弄

以上所有內容都保留了元素的原始順序。

Postgres 9.3 或更高版本

使用功能json_array_elements()。但是我們從中得到雙引號字元串。

在外部查詢中具有聚合的替代查詢。CROSS JOIN刪除缺少數組或空數組的行。也可用於處理元素。我們需要一個唯一的鍵來聚合：

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY 建構子，仍然帶有引號的字元串：

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

請注意null，與上面不同，它轉換為文本值“null”。不正確，嚴格來說，並且可能模棱兩可。

窮人不引用trim()：

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

從 tbl 中檢索一行：

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

字元串形成相關子查詢：

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
               FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

數組建構子：

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                    FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>在這裡擺弄。

老sqlfiddle

有關的：

• 需要從 PostgreSQL 表中動態選擇 JSON 數組元素

原始註釋（自第 9.4 頁起已過時）

我們需要一個***json_array_elements_text(json)***, 的雙胞胎從 JSON 數組json_array_elements(json)返回正確的值。text但這似乎從提供的 JSON 函式庫中消失了。text或其他一些從標量值中提取值的json函式。我好像也錯過了那個。

所以我即興創作trim()，但對於非平凡的案例將失敗……

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