在Azure Databricks中为内联函数提取的列设别名及相关问题

背景信息 #

我有一个保存为JSON格式的metadata字段，结构如下：

{"IDH": [{"ID": "ABCD", "name": "1111"},
         {"id": "EFGH", "name": "2222"}], 
 "RSN": "1000"}

编写了以下SQL语句提取数据：

SELECT inv.InventoryId, inv.Metadata, inv.Metadata:RSN as `RSN`,  
  INLINE
  (
    FROM_JSON( UPPER(inv.Metadata:IDH[*]), 'array<struct<ID:string,NAME:string>>')
  )
  FROM inventory inv

待解决的问题 #

1. JSON内的属性字段大小写不一致（如ID和id），目前通过转大写处理，但会改变值的大小写，需要实现忽略属性大小写或映射属性名称的效果，同时保留值的原始大小写。
2. 希望修改JSON内嵌列的提取别名，例如将ID提取为IDH_ID，name提取为IDH_Name。之前用CTE实现但查询耗时大幅增加，想在当前查询内直接解决，同时兼容第一个问题。

解决方案 #

以下以Snowflake SQL为例（适配你当前使用的语法风格），直接在原查询结构内解决两个问题，无需额外CTE：

方案1：通过FLATTEN+COALESCE处理大小写并指定别名

SELECT 
    inv.InventoryId, 
    inv.Metadata, 
    inv.Metadata:RSN AS `RSN`,
    INLINE(
        SELECT 
            -- 同时匹配大小写不同的ID字段，保留原始值大小写
            COALESCE(idh_obj.value:ID::string, idh_obj.value:id::string) AS IDH_ID,
            idh_obj.value:name::string AS IDH_Name
        FROM LATERAL FLATTEN(input => inv.Metadata:IDH) idh_obj
    )
FROM inventory inv

方案2：结合FROM_JSON的大小写不敏感解析+别名定义

如果你的SQL引擎支持JSON解析时忽略键的大小写（如Snowflake的CASE_INSENSITIVE参数），可以用这种更简洁的方式：

SELECT 
    inv.InventoryId, 
    inv.Metadata, 
    inv.Metadata:RSN AS `RSN`,
    INLINE(
        -- 解析时忽略键的大小写，同时指定目标结构体字段
        FROM_JSON(
            inv.Metadata:IDH[*],
            'array<struct<ID:string, name:string>>',
            CASE_INSENSITIVE => TRUE
        )
        -- 直接给字段重命名
        SELECT ID AS IDH_ID, name AS IDH_Name
    )
FROM inventory inv

说明 #

• 大小写兼容：方案1用COALESCE显式匹配不同大小写的字段，完全保留值的原始大小写；方案2利用引擎的大小写不敏感解析能力，自动映射不同大小写的键到目标字段。
• 别名优化：两种方案都在INLINE内部的子查询中直接指定别名，避免了CTE带来的额外性能开销，保持查询的简洁高效。

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