如何从JSON列提取匹配货币列的对应汇率值？

针对你描述的场景——关联销售表和每日汇率表，根据销售记录的货币类型从JSON格式的汇率列中提取对应值，下面按主流数据库给出高效的实现方案，替代冗长的CASE WHEN语句：

PostgreSQL 实现 #

利用PostgreSQL原生的JSON操作符->>，直接通过字段值动态引用JSON键：

SELECT
    s.currency,
    s.cost,
    s.date_of_sale,
    -- 提取对应货币的汇率（文本转数值）
    (r.rates_values ->> s.currency)::NUMERIC AS exchange_rate,
    -- 可选：计算转换为欧元的金额
    s.cost::NUMERIC / (r.rates_values ->> s.currency)::NUMERIC AS euro_equivalent
FROM
    sales s
JOIN
    daily_exchange_rates r ON s.date_of_sale = r.record_date;

注意：如果JSON中的货币键与s.currency大小写不一致，可通过字符串函数统一，比如r.rates_values ->> LOWER(s.currency)。

MySQL 实现 #

使用->>简化操作符结合动态拼接的JSON路径：

SELECT
    s.currency,
    s.cost,
    s.date_of_sale,
    -- 动态拼接JSON路径并提取值
    r.rates_values ->> CONCAT('$.', s.currency) AS exchange_rate,
    -- 可选：转换为欧元金额
    s.cost / (r.rates_values ->> CONCAT('$.', s.currency)) AS euro_equivalent
FROM
    sales s
JOIN
    daily_exchange_rates r ON s.date_of_sale = r.record_date;

->>操作符会自动去除JSON值的引号，直接返回文本格式的汇率值，可直接参与数值计算。

SQL Server 实现 #

借助JSON_VALUE函数动态生成路径提取值：

SELECT
    s.currency,
    s.cost,
    s.date_of_sale,
    -- 提取汇率并转换为数值类型
    CAST(JSON_VALUE(r.rates_values, CONCAT('$.', s.currency)) AS DECIMAL(18,6)) AS exchange_rate,
    -- 可选：计算欧元等价金额
    s.cost / CAST(JSON_VALUE(r.rates_values, CONCAT('$.', s.currency)) AS DECIMAL(18,6)) AS euro_equivalent
FROM
    sales s
JOIN
    daily_exchange_rates r ON s.date_of_sale = r.record_date;

补充优化：若存在JSON中无对应货币键的情况，可使用COALESCE设置默认值（比如欧元本身汇率为1）：

COALESCE(CAST(JSON_VALUE(r.rates_values, CONCAT('$.', s.currency)) AS DECIMAL(18,6)), 1) AS exchange_rate

通用注意事项 #

• 确保s.currency的取值与JSON中的键完全匹配（拼写、大小写），避免提取结果为NULL
• 根据实际业务需求选择合适的数值精度（比如DECIMAL(18,6)或NUMERIC(12,4)）
• 若需处理历史数据中JSON格式不一致的情况，可先通过JSON_VALID函数校验合法性

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