用連接表中的字元串替換 YAML 數組的簡單方法

我有一個充滿營地數據的數據庫。每個營地都有很多露營者，並且有很多 camp_weeks。

在營員表中，有一個 YAML 編碼的營員營週 ID 數組。該項目需要查詢才能顯示。這是露營者表中的範例行：

19770,'Daniela',41,'---
- 406
- 407
')

我目前的查詢（基於此部落格的優秀提示）：

SELECT 
 campers.name,
 camps.id,
 camps.title,
 regexp_split_to_array(trim(regexp_replace(campers.camp_weeks, E'[\\n\\r''-]+', ' ', 'g' )), E'\\D+') AS week_array
FROM 
 campers
LEFT JOIN camps ON campers.camp_id = camps.id
GROUP BY camps.id, campers.id

返回露營者，帶有如下營地 ID 的字元串/數組：

====================================
|  Daniela | 41 | sports | 406,407 |
====================================

我希望查詢返回

============================================
|  Daniela | 41 | sports | cricket, tennis |
============================================

有什麼簡單的方法可以做到這一點？

性能對這個查詢來說不是一個大問題，查詢越簡單越好。

這是一個SQL Fiddle。

Colin ’t Hart的評論 100% 正確 - 您的數據模型在ACID的第一個字母上失敗。YAML 非常適合用於應用程序消費的對象序列化，但由於您的數據層是 RDBMS，因此您應該堅持使用 RDBMS 範例。假設您無權遷移到更好的結構並可能強制重新編碼應用程序，好消息是您的原始查詢已經完成了一半：而不是直接進入數組 ( regexp_split_to_array)，regexp_split_to_table而是拆分到表 ( )，然後camp_weeks對名稱執行連接。然後，您可以將其分組為填充字元串列表（使用string_agg如果它在您的 postgres 版本上可用，如下所示），或者按原樣傳遞結果，讓應用程序擔心表示層。

SELECT  a.name,
       a.id,
       a.title,
       string_agg( cw.week_name, ', ' )
            AS week_names
FROM (  SELECT  cs.id
                   AS camper_id,
               cs.name,
               c.id,
               c.title,
               regexp_split_to_table( trim( 
                   regexp_replace( 
                       cs.camp_weeks, 
                       E'[\\n\\r''-]+',
                       ' ', 'g' ) 
                   ), E'\\D+' ) 
                   AS camp_week_id
       FROM    public.campers cs
       LEFT JOIN public.camps c
           ON  cs.camp_id = c.id ) a
LEFT JOIN public.camp_weeks cw
   ON  CAST( a.camp_week_id AS INTEGER ) = cw.id
GROUP BY a.name, a.id, a.camper_id, a.title
ORDER BY a.id, a.camper_id;

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