Snowflake使用LATERAL FLATTEN展平变长JSON丢失记录如何处理

你遇到的记录丢失问题是Snowflake默认LATERAL FLATTEN为内连接行为导致的：当待展平的字段（这里是crossListedCourses）不存在、值为null或为空数组时，内连接会直接过滤掉对应原始记录。

要保留所有原始记录，只需将LATERAL FLATTEN改为外连接模式即可，有两种等价写法可选： #

写法1：直接加OUTER关键字（语法更直观，效果等价于LEFT JOIN） #

SELECT 
    json_string:course.className AS course_className,
    json_string:course.courseType AS course_courseType,
    json_string:course.crn AS course_crn,
    json_string:course.crossListedCourses AS crossListedCourses,
    prod.value:className AS cross_className,
    prod.value:courseType AS cross_courseType,
    prod.value:crn AS cross_crn,
    prod.value:lmsCourseID AS cross_lmsCourseID,
    prod.value:partialTermCode AS cross_partialTermCode,
    prod.value:subject AS cross_subject,
    prod.value:subjectCode AS cross_subjectCode,
    prod.value:term AS cross_term
FROM variant_example, 
OUTER LATERAL FLATTEN (input => json_string:course.crossListedCourses) AS prod

写法2：在FLATTEN参数中指定OUTER => TRUE（效果和写法1完全一致） #

SELECT 
    json_string:course.className AS course_className,
    json_string:course.courseType AS course_courseType,
    json_string:course.crn AS course_crn,
    json_string:course.crossListedCourses AS crossListedCourses,
    prod.value:className AS cross_className,
    prod.value:courseType AS cross_courseType,
    prod.value:crn AS cross_crn,
    prod.value:lmsCourseID AS cross_lmsCourseID,
    prod.value:partialTermCode AS cross_partialTermCode,
    prod.value:subject AS cross_subject,
    prod.value:subjectCode AS cross_subjectCode,
    prod.value:term AS cross_term
FROM variant_example, 
LATERAL FLATTEN (input => json_string:course.crossListedCourses, OUTER => TRUE) AS prod

调整后第三条没有crossListedCourses的记录会完整保留，所有和交叉课程相关的字段会返回null值，不会被过滤。另外原查询中json_string:crossListedCourses路径错误，已修正为json_string:course.crossListedCourses，你可以根据实际需求调整字段别名。

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