Postgresql
將不同 JSON 鍵映射到相同目標列的最佳方法
我有來自多個供應商的 CSV 導入數據,有 50 多個不同的列,每個供應商的命名略有不同。
我不想花費數小時將所有列名映射到包含大量列的主表,而是想將原始 CSV 行儲存為 JSON 列,例如
csv_data
.我還想儲存一個由所有供應商共享的唯一標識符,例如
所以我的“聯繫人”表中的列將是:
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
而不是……