Easy way to replace an YAML array of IDs with strings from a joined table I have a database full of camp data. Each camp has many campers and has many camp_weeks.

In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project requires a query for display. Here's a sample row from the campers table:

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


My current query (based on the excellent tip from this blog):

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


returns the campers, with a string/array of camp ids like this:

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


I would like the query to return

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


What would be an easy way to do this?

Performance is not a big issue for this query and the simpler the query is the better it will be.

Here's a SQL Fiddle.
Colin 't Hart's comment is 100% correct - your data model fails on the first letter of ACID.  YAML is fine for serialization of objects for application consumption, but since your data layer is a RDBMS, you should stick to the RDBMS paradigms.  Assuming you don't have the permission to migrate to a better structure and probably force a re-code of the application, the good news is that you're halfway there with your original query already:  Instead of going straight to array ( regexp_split_to_array ), split to table ( regexp_split_to_table ) instead, then perform a join on camp_weeks for the names.  You could then group that for the stuffed string listing ( using string_agg if it's available on your version of postgres, like shown below ), or pass the results as-is, letting the application worry about the presentation layer.

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;