基于Lateral Join的SQL过滤：嵌套位置码数据筛选问题

现有存储产品ID与嵌套格式位置码的数据库表，表结构如下：

已使用以下Lateral Flatten查询：

SELECT a.product_id, f.* FROM table AS a INNER JOIN LATERAL FLATTEN (input => a.location_codes) AS f

得到结果：

需要解决两个问题：

1. 如何过滤出仅含South编码的行？
2. 如何筛选仅包含编码south2的结果？

1. 过滤出仅含South编码的行 #

直接在查询末尾添加WHERE子句，匹配f.key的值即可：

SELECT a.product_id, f.* 
FROM table AS a 
INNER JOIN LATERAL FLATTEN (input => a.location_codes) AS f
WHERE f.key = 'SOUTH' -- 注意大小写需和数据中的key保持一致

这段代码会只保留key为SOUTH的行，也就是对应South区域的位置码集合。

2. 筛选仅包含编码south2的结果 #

当前的Flatten仅拆分了外层的区域键（WEST/EAST/SOUTH），每个区域对应的是位置码数组，需要二次Flatten拆分数组中的单个位置码，再进行过滤：

步骤1：二次Flatten拆分位置码数组 #

SELECT a.product_id, f1.key AS region, f2.value AS location_code
FROM table AS a
INNER JOIN LATERAL FLATTEN(input => a.location_codes) AS f1
INNER JOIN LATERAL FLATTEN(input => f1.value) AS f2

执行后会得到每个产品对应的单个位置码：

步骤2：添加WHERE子句筛选south2 #

在上述查询基础上添加过滤条件：

SELECT a.product_id, f1.key AS region, f2.value AS location_code
FROM table AS a
INNER JOIN LATERAL FLATTEN(input => a.location_codes) AS f1
INNER JOIN LATERAL FLATTEN(input => f1.value) AS f2
WHERE f2.value = 'south2'

这样就会只保留位置码为south2的行。

如果需要仅返回产品中只包含south2这一个位置码（排除存在其他位置码的产品），可以用分组加条件判断：

SELECT a.product_id
FROM table AS a
INNER JOIN LATERAL FLATTEN(input => a.location_codes) AS f1
INNER JOIN LATERAL FLATTEN(input => f1.value) AS f2
GROUP BY a.product_id
HAVING COUNT(DISTINCT f2.value) = 1 AND MAX(f2.value) = 'south2'

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