如何从SQL表中查询并以表中列作为键实现映射匹配？

给定主表 main #

映射定义 #

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

目标输出 #

需要生成包含abc、pqr、lang和对应映射索引的表：

问题场景 #

你已经能单独从映射中获取单个索引值：

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

但尝试关联main表时出现语法错误，错误语句：

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

正确解法 #

可以通过CROSS JOIN将主表与映射表关联（因为映射表只有一行数据，CROSS JOIN不会产生多余数据），具体语句如下：

WITH mappings AS (
    SELECT MAP(ARRAY['en_fr', 'en_de'], ARRAY['1', '2']) AS lang2idx
)
SELECT 
    main.abc,
    main.pqr,
    main.lang,
    mappings.lang2idx[main.lang] AS index
FROM main
CROSS JOIN mappings;

也可以将映射定义直接作为子查询嵌入SELECT语句中：

SELECT 
    abc,
    pqr,
    lang,
    (
        SELECT lang2idx[main.lang] 
        FROM (
            SELECT MAP(ARRAY['en_fr', 'en_de'], ARRAY['1', '2']) AS lang2idx
        ) AS mappings
    ) AS index
FROM main;

错误原因说明 #

你之前的错误写法(lang2idx[lang] FROM MAPPINGS)不符合SQL语法规范，无法直接在SELECT字段列表中这样嵌套查询。必须通过表关联（如CROSS JOIN）或者正确的子查询语法来引用映射表中的数据。

内容的提问来源于stack exchange，提问作者alvas