Postgresql

將不同 JSON 鍵映射到相同目標列的最佳方法

  • March 14, 2022

我有來自多個供應商的 CSV 導入數據,有 50 多個不同的列,每個供應商的命名略有不同。

我不想花費數小時將所有列名映射到包含大量列的主表,而是想將原始 CSV 行儲存為 JSON 列,例如csv_data.

我還想儲存一個由所有供應商共享的唯一標識符,例如email.

所以我的“聯繫人”表中的列將是:

vendor_id
email
csv_data

然後我希望我可以創建一個視圖或物化視圖,我可以從我知道我需要的一小部分列開始(並且會花時間映射)。然後,如果我以後需要新列,我可以使用新映射修改視圖。

我希望我可以做一些案例切換,像這樣:

SELECT
 CASE 
   WHEN vendor_id = 100 THEN
       csv_data->>'fullname' AS full_name,
       csv_data->>'age' AS age,
       ... etc ...
   WHEN vendor_id = 101 THEN
       csv_data->>'FULL NAME' AS full_name,
       csv_data->>'AGE' AS age,
       ... etc ...
   WHEN vendor_id = 102 THEN
       csv_data->>'full name' AS full_name,
       csv_data->>'cust_age' AS age,
       ... etc ...
 END
FROM contacts

不幸的是,這是一個語法錯誤……有沒有更理智的方法來做到這一點?我想不出好主意。我真的不想創建一些巨大的映射,因為很多列名都很亂,可能永遠不會被使用。我想儲存數據以備不時之需,但對於我的“結果”查詢,我想創建一個漂亮的干淨映射,我只在需要時才添加它。

我也願意接受更好的方式來做這件事。我試圖避開巨大的桌子的核路線。但我想如果我絕對建議是最好的方法,我會的。

如果你走這條路,儲存原始 JSON 數據(我建議jsonb而不是json- 更清潔和更快地提取),然後考慮一個轉換錶,其中包含每個供應商每個相關目標列的鍵名。方便地作為另一個單一jsonb。像:

CREATE TABLE key2col (
 vendor_id int PRIMARY KEY
, keys jsonb NOT NULL
);

INSERT INTO key2col VALUES
 (100, '{"full_name":"fullname", "age":"age"}')
, (101, '{"full_name":"FULL NAME", "age":"AGE"}')
-- more ...
;

那麼您的查詢可以簡單地是:

SELECT c.vendor_id, c.email
    , c.csv_data->>(k.keys->>'full_name') AS full_name
    , c.csv_data->>(k.keys->>'age') AS age
FROM   contacts c
LEFT   JOIN key2col k USING (vendor_id);

db<>在這裡擺弄

這甚至適用於任何不同的鍵集 –> 每個供應商的列翻譯。每個沒有給定供應商的 JSON 鍵的欄位都key2col.keys將是NULL. 如果供應商實際上沒有提供聲明的密鑰,則相同contacts.csv_data

查詢中的 保留來自聯繫人的LEFT JOIN行,沒有任何註冊的列。你可能想要一個平原JOIN而不是……

引用自:https://dba.stackexchange.com/questions/308684