此 string_to_array 的唯一數組值

這是對以下內容的跟進：

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

基於這些範例表：

data_providers:
id | field_map
--------------
1  | {"segments": "SEGMENT IDS", "full_name": "FULL NAME"}

leads:
id | data_provider_id | email | data
------------------------------------
1  | 201              | hi@hi | {"SEGMENT IDS": "id1,id1,id1,id2,id3", "FULL NAME": "John Doe"}
2  | 201              | xx@xx | {"FULL NAME": "Billy Bob"}

desired output:

data_provider_id | email | full_name | segment
----------------------------------------------
201              | hi@hi | John Doe  | id1
201              | hi@hi | John Doe  | id2
201              | hi@hi | John Doe  | id3
201              | xx@xx | Billy Bob | NULL

我有以下查詢：

SELECT
 leads.data_provider_id,
 leads.email,
 leads.data->>(p.field_map->>'full_name') AS full_name,
 segment
FROM leads
LEFT OUTER JOIN data_providers p ON p.id = leads.data_provider_id
LEFT JOIN LATERAL unnest(string_to_array(leads.data->>(p.field_map->>'segments'), ',')) AS segment ON true

此查詢正在做 2 件特定的事情：

1. 如果 CSV 列標題，則它加入data_providers表以獲取field_map包含 JSONB 映射的列。所以像{"segments": "SEGMENT ID", "full_name": "FULL NAME"}
2. 在 的dataJSONB 列中leads，有一個鍵（我通過上面的欄位映射發現）包含一個逗號分隔的 segment_id 字元串（它以 CSV 格式出現，他們選擇在 1 行中放置 2 個值）。我想拆分它，以便每個 segment_id 都有自己的行（顯然所有其他列在兩行上都保持不變）。

我有兩個目標：

1. 如果有一個空字元串或映射中不存在鍵，我想返回該行，但segment_id 為NULL。我已經通過更改CROSS JOIN為LEFT JOIN.
2. 我正在嘗試刪除段 id 中的重複項，因此如果有人輸入 ‘id1,id1’ 它應該只產生 1 行。我這樣做是因為物化視圖的該列上有一個唯一索引。

我目前停留在＃2。

使其成為子查詢並輸入DISTINCT：

SELECT l.data_provider_id
    , l.email
    , l.data->>(p.field_map->>'full_name') AS full_name
    , s.segment
FROM   leads l
LEFT   JOIN data_providers p ON p.id = l.data_provider_id
LEFT   JOIN LATERAL (
  SELECT DISTINCT segment
  FROM   unnest(string_to_array(l.data->>(p.field_map->>'segment'), ',')) AS segment
  ) s ON true

順便說一句，您field_map持有關鍵的“細分”，而不是“細分”。

你甚至可以使用這個簡短的語法：

...
LEFT   JOIN LATERAL (
  SELECT DISTINCT unnest(string_to_array(l.data->>(p.field_map->>'segment'), ','))
  ) s(segment) ON true

（但最後一個可能會讓毫無戒心的 SQL 純粹主義者畏縮。）

不保留數組元素的原始順序。如果您需要，請參閱：

• 如何保留未嵌套數組中元素的原始順序？

並使用GROUP BY而不是DISTINCT聚合每組重複項的最小序數位置。

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