使用lateral flatten函数时获取variant列JSON中最大offset的方法

首先明确结论：你需要先完成JSON数据的打平解析后才能对Offset字段做最大值筛选，Offset嵌套在Variant类型的Record_Key字段中，未解析打平前无法直接用于最大值判断。CTE和子查询两种方式都可以实现，CTE的可读性更高，更推荐使用。

核心逻辑 #

• 先用LATERAL FLATTEN函数对Record_Content列做打平，按需对Record_Key列做打平（如果是JSON数组结构的话），解析出Key_ID、Offset以及打平后的Payload内容
• 基于解析完成的Offset字段做最大值筛选，支持全表取最大Offset，也支持按Key_ID分组取每个Key对应的最大Offset

推荐实现（CTE方式，可读性最高） #

WITH flattened_data AS (
    SELECT
        -- 解析Record_Key中的字段，如为数组需先打平Record_Key再取值
        r.Record_Key:Key_ID::STRING AS key_id,
        r.Record_Key:Offset::NUMBER AS offset_val,
        -- 打平Record_Content后获取单条Payload内容
        f.value AS content_payload,
        -- 保留原表其他需要的字段，不需要可删除
        r.* EXCLUDE (Record_Key, Record_Content)
    FROM your_table_name r
    -- 打平Record_Content列的JSON数组
    , LATERAL FLATTEN(input => r.Record_Content) f
    -- 若Record_Key为JSON数组需打平，取消下方注释即可
    -- , LATERAL FLATTEN(input => r.Record_Key) k
)
-- 筛选全表Offset最大的记录
SELECT *
FROM flattened_data
WHERE offset_val = (SELECT MAX(offset_val) FROM flattened_data);

-- 若需要按每个key_id取对应最大Offset的记录，替换上述SELECT语句为以下内容即可
-- SELECT *
-- FROM flattened_data
-- QUALIFY ROW_NUMBER() OVER (PARTITION BY key_id ORDER BY offset_val DESC) = 1;

子查询实现（无需CTE） #

可以直接用子查询实现，但是打平逻辑还是要嵌套在子查询中，示例如下：

SELECT *
FROM (
    SELECT
        r.Record_Key:Key_ID::STRING AS key_id,
        r.Record_Key:Offset::NUMBER AS offset_val,
        f.value AS content_payload,
        r.* EXCLUDE (Record_Key, Record_Content)
    FROM your_table_name r
    , LATERAL FLATTEN(input => r.Record_Content) f
) flattened_data
WHERE offset_val = (SELECT MAX(Record_Key:Offset::NUMBER) FROM your_table_name);

注意：如果Record_Key是JSON数组结构，不能直接在子查询中取原表的MAX(Record_Key:Offset)，该写法会取到单条数据数组内的最大值，而不是打平后所有行的最大值，必须先打平Record_Key再做最大值计算。

注意事项 #

• 字段类型转换时要和实际存储的Offset类型匹配，避免出现数值精度损失或者转换报错
• 如果Record_Content是单级JSON对象而非数组，LATERAL FLATTEN可以加path参数指定要打平的嵌套层级，或者直接用Record_Content:字段名的方式取值，不需要打平
• 如果筛选后有重复数据，可以加DISTINCT去重，或者用QUALIFY语法搭配窗口函数实现更灵活的分组去重

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