如何用FLATTEN实现JSON变体列属性的去重ID计数统计？

针对你需要按JSON列的属性统计去重ID数量的需求，使用FLATTEN函数可以轻松实现，以下是完整的SQL查询及说明：

完整查询语句 #

WITH Table1 AS (
    SELECT 'A123' AS ID, PARSE_JSON('{"ATTR1":"Y", "ATTR2":"Y","ATTR3":"Y"}') AS ATTR UNION ALL
    SELECT 'B234' AS ID, PARSE_JSON('{"ATTR1":"Y", "ATTR2":"Y","ATTR3":"Y"}') AS ATTR UNION ALL
    SELECT 'C567' AS ID, PARSE_JSON('{"ATTR1":"Y", "ATTR2":"Y","ATTR3":"Y"}') AS ATTR UNION ALL
    SELECT 'D019' AS ID, PARSE_JSON('{"ATTR1":"Y", "ATTR2":"Y","ATTR3":"Y"}') AS ATTR UNION ALL
    SELECT 'E923' AS ID, PARSE_JSON('{"ATTR1":"Y", "ATTR2":"Y"}') AS ATTR
)
SELECT 
    f.key::STRING AS "ATTR_FLAT",
    COUNT(DISTINCT t.ID) AS "count(distinct ID)"
FROM Table1 t,
LATERAL FLATTEN(input => t.ATTR, mode => 'OBJECT') f
GROUP BY ATTR_FLAT
ORDER BY ATTR_FLAT;

关键部分说明 #

• LATERAL FLATTEN：通过mode => 'OBJECT'参数，将JSON对象的每个键值对拆分为独立行，每行包含属性名（key）和属性值（value）。LATERAL关键字确保每行原始数据都能被正确展开。
• 属性名提取：用f.key::STRING将JSON键转换为字符串类型，作为最终的ATTR_FLAT列。
• 分组统计：按ATTR_FLAT分组后，使用COUNT(DISTINCT t.ID)计算每个属性对应的去重ID数量。

查询结果 #

执行上述语句后，将得到你期望的结果：

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