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

我有來自多個供應商的 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