（PostgreSQL 11）將 JSON 對象（或複合類型）數組從 CSV 文件讀入 Postgres

因此，我嘗試使用 CSV 文件將行導入表中，但遇到了我無法解決的問題。我希望一行的一列是 JSON 對象的數組。

我通常希望我的桌子看起來像：

我選擇不為選項創建單獨的表，因為我真的不需要在我的應用程序中這樣做，所以如果可能的話，我希望不必這樣做。

我對錶格的定義：

CREATE TABLE statements(
statement_id INT GENERATED BY DEFAULT AS IDENTITY,
statement VARCHAR(100) NOT NULL,
options JSON [],
PRIMARY KEY(statement_id)
);

我如何將 CSV 文件導入表中：

copy statements(statement_id, statement, options)
from 'D:\Projects\project\files\statements.csv'
delimiter ','
csv header;

如何在我的 CSV 中格式化選項：

{("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)}

嘗試 1

使用我上面提到的設置，這是我得到的錯誤：

ERROR:  malformed array literal: "{("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)}"
DETAIL:  Unexpected array element.
CONTEXT:  COPY asmt_questions, line 2, column options: "{("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately T..."

我找到了這個解決方案，並將 CSV 中的選項格式編輯為：

array[("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)]::json[]

並得到以下錯誤：

ERROR:  malformed array literal: "array[("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)]::json[]"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  COPY asmt_questions, line 2, column options: "array[("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderat..."

嘗試 2

我找不到與這個問題相關的任何解決方案，但我確實注意到我上面提到的解決方案使用了複合類型，所以我創建了一個複合類型：

CREATE TYPE statement_option AS (
   label VARCHAR(20),
   value INT
);

並將語句表模式更改為

CREATE TABLE statements(
   statement_id INT GENERATED BY DEFAULT AS IDENTITY,
   statement VARCHAR(100) NOT NULL,
   options statement_option [],
   PRIMARY KEY(statement_id)
   );

我得到了上面列出的相同錯誤。

嘗試 n

在嘗試尋找這些錯誤的解決方案後，我找不到針對這種情況的解決方案（其中很多與過程和函式有關）。所以我嘗試了上述設置的許多不同組合。

我嘗試使用方括號來表示 CSV 中的數組（例如：） ，我在語句[{},{}]表模式中更改為[]，我在 CSV 文件中使用單引號而不是雙引號等。一切都無濟於事。ARRAY

在這一點上，我覺得當涉及到這個特定設置時，我已經用盡了我可以嘗試的選項。我有一種預感，我可能不得不逐行執行，或者只是創建一個程序來讀取它們。

但我想知道是否有我遺漏的東西，或者我做錯了什麼？

這對我有用：

我的 CSV

id;json
1;{"{\"label\": \"Not at all True\",\"value\": 1}","{\"label\": \"Hardly True\",\"value\": 2}","{\"label\": \"Moderately True\",\"value\": 3}","{\"label\": \"Exactly True\",\"value\": 4}"}
2;{"{\"label\": \"Not at all True\",\"value\": 1}","{\"label\": \"Hardly True\",\"value\": 2}","{\"label\": \"Moderately True\",\"value\": 3}","{\"label\": \"Exactly True\",\"value\": 4}"}

我的表：

create table test_json_list (id integer, j json[]);

我的副本聲明：

COPY test_json_list (id, j) FROM '/tmp/json.csv' WITH (DELIMITER ';', HEADER, FORMAT CSV, QUOTE '|');

解釋：PostgresSQL 中的數組字面量是花括號之間的元素列表，例如：{1,2,3} 或 {‘a’,‘b’,‘c’}

我沒有在文件中找到一個明確的解釋，為什麼該COPY命令希望將 JSON 數組元素用雙引號引起來，只是將COPY TO命令的輸出作為參考。

我添加了一個與您的 JSON 沒有太大衝突的分隔符，但它不是很重要。

我添加了一個管道 QUOTE 字元以避免額外的雙引號轉義級別，因為預設的 CSV 引號字元也是雙引號，否則這將是必需的。

如果可能的話，我的建議是通過應用程序邏輯導入 JSON 數組而不是 JSON 的 ARRAY（Postgres 數組）來稍微簡化這一切。

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