如何在Snowflake SQL中从数组字段提取指定值？

根据你的需求，这里提供几种不同场景下的实现方式：

1. 生成包含指定值的新数组 #

如果需要从原数组中提取出'abc'和'the'，并生成一个只包含这两个值的新数组字段，可以使用ARRAY_INTERSECT函数，它会返回两个数组的交集：

SELECT 
    ARRAY_INTERSECT(FieldName, ['abc', 'the']::VARIANT) AS extracted_target_values,
    *
FROM table1;

针对示例中的数组['abc','cbs','the','lgj']，这个查询会返回新字段值为['abc','the']。

2. 分别生成每个指定值的存在标记字段 #

如果需要单独标记原数组中是否包含'abc'和'the'，可以多次使用ARRAY_CONTAINS生成布尔类型的字段：

SELECT 
    ARRAY_CONTAINS('abc'::VARIANT, FieldName) AS has_abc,
    ARRAY_CONTAINS('the'::VARIANT, FieldName) AS has_the,
    *
FROM table1;

查询结果会新增两个字段：has_abc（存在'abc'则为TRUE，否则为FALSE）和has_the（同理）。

3. 将提取到的值展开为多行 #

如果需要把匹配的每个值单独拆分成一行展示，可以结合LATERAL FLATTEN和WHERE条件过滤：

SELECT 
    f.value AS matched_value,
    t.*
FROM table1 t,
LATERAL FLATTEN(input => t.FieldName) f
WHERE f.value IN ('abc', 'the');

针对示例数据，这个查询会生成两行结果，分别对应'abc'和'the'，每行保留原表的所有字段。

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