How do I do a selection from table and a mapping using one of the column from the table as key?

Question

Given the table main:

abc	pqr	price	lang
hello	hallo	2.34	en_de
world	welt	1.23	en_de
coffee	cafe	9.23	en_fr

And a map that looks like:

with mappings AS (
    SELECT MAP (
        ARRAY['en_fr', 'en_de'],
        ARRAY['1', '2']
    ) AS lang2idx
)

The goal is to extract a table where the output

abc	pqr	lang	index
hello	hallo	en_de	2
world	welt	en_de	2
coffee	cafe	en_fr	1

I've tried this which fetches the index id I need from the mapping:

with mappings AS (
    SELECT MAP (
        ARRAY['en_fr', 'en_de'],
        ARRAY['1', '2']
    ) AS lang2idx
)
SELECT lang2idx['en_fr'] FROM mappings

I tried to do some selection from different tables, it kind of goes haywire, it throws a syntax error:

SELECT abc, pqr, lang, (lang2idx[lang] FROM MAPPINGS) FROM main

How do I do a selection from table and a mapping to produce the desired output like above?

Answer 1

Personally I would go with "simple" CTE and join. Something along these lines:

with mappings (lang, index) AS (
    values ('en_fr', '1'),
           ('en_de', '2')
)

SELECT m.abc, 
  m.pqr, 
  m.lang, 
  mm.index
FROM main m
LEFT JOIN mappings mm on mm.lang = m.lang 

If you want to keep the original CTE as is, you can use unnest:

WITH mappings AS (
  SELECT MAP (
    ARRAY['en_fr', 'en_de'],
    ARRAY['1', '2']
  ) AS lang2idx
),
unnested as (
   SELECT t.*
   FROM mappings,
   unnest (lang2idx) as t(lang, index)
)

SELECT  m.abc, 
  m.pqr, 
  m.lang, 
  mm.index
FROM main m
LEFT JOIN unnested mm on mm.lang = m.lang 

And another option, closest to your attempt (assumes only one row in the mappings) - join with selection by key:

SELECT abc, 
  pqr, 
  lang, 
  element_at(mm.lang2idx, m.lang) index
FROM main m
JOIN mappings mm on true -- or cross join mappings mm