如何在Snowflake中筛选不含黑名单标签的JSON数组行？

问题分析 #

你需要从libraries表中筛选出所有专辑的label都不属于blocked_labels黑名单的记录，但原查询通过LATERAL FLATTEN后过滤，会导致只要行内存在合规专辑就被保留，无法排除包含黑名单label的行。

正确查询方案 #

方案一：使用NOT EXISTS子查询（最直观） #

通过子查询检查当前行的专辑中是否存在黑名单label，不存在则保留该行：

SELECT albums
FROM libraries
WHERE NOT EXISTS (
    SELECT 1
    FROM LATERAL FLATTEN(input => albums) flattened_album
    WHERE flattened_album.value:label::STRING IN (SELECT label_name FROM blocked_labels)
);

方案二：左连接FLATTEN后分组判断 #

通过左连接匹配黑名单label，分组后统计匹配数为0的行（即无任何专辑在黑名单中）：

SELECT l.albums
FROM libraries l
LEFT JOIN LATERAL FLATTEN(input => l.albums) a
ON a.value:label::STRING IN (SELECT label_name FROM blocked_labels)
GROUP BY l.albums
HAVING COUNT(a.value) = 0;

方案三：使用ARRAY_REDUCE数组函数 #

利用Snowflake的数组聚合函数，逐个检查专辑label是否不在黑名单中：

WITH blocked_array AS (
    SELECT ARRAY_AGG(label_name) AS blocked_list
    FROM blocked_labels
)
SELECT l.albums
FROM libraries l, blocked_array ba
WHERE ARRAY_REDUCE(
    l.albums,
    TRUE,
    (accumulator, album) => accumulator AND NOT ARRAY_CONTAINS(ba.blocked_list, album:label::STRING)
);

原查询问题说明 #

原查询使用LATERAL FLATTEN后直接过滤黑名单label，本质是筛选出单个合规的专辑记录，而非整行所有专辑都合规的记录。这会导致：

• 同一行中只要有一个合规专辑，就会生成一条该行的记录
• 即使行内包含黑名单专辑，只要存在合规专辑就会被保留，不符合需求

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