Spark SQL验证JSON数组内share_id长度的结果生成需求

需求说明 #

• 需验证JSON数组elements中每个对象的share_id字符串长度
• 若存在任意一个share_id长度>20，该记录的validation结果为0；若所有share_id长度均<=20，则结果为1

表结构 #

已尝试的查询及问题 #

查询1：提取share_id数组 #

query = """
SELECT agent, elements.elements.share_id as share
FROM tempTable
WHERE data = '2025-03-31'
"""

result_df = spark.sql(query)
display(result_df)

结果：

查询2：仅获取第一个元素长度 #

query = """
SELECT length(element_at(elements.elements.share_id, 1)) as validation
FROM tempTable
WHERE data = '2025-03-31'
AND data_timestamp = '20250604150536'
"""

result_df = spark.sql(query)
display(result_df)

结果：

问题：仅能获取数组第一个元素的长度，无法检查所有元素。

期望最终结果 #

解决方案 #

提供两种高效的Spark SQL实现方式：

方法1：使用高阶函数ARRAY_EXISTS（推荐，无需展开数组） #

该函数直接在数组上判断是否存在满足条件的元素，性能更优：

SELECT 
    agent,
    IF(ARRAY_EXISTS(elements.elements, elem -> LENGTH(elem.share_id) > 20), 0, 1) AS validation
FROM tempTable
WHERE data = '2025-03-31'

方法2：展开数组后聚合判断 #

通过LATERAL VIEW EXPLODE展开数组，计算每个share_id的长度，再分组聚合判断：

SELECT 
    agent,
    CASE 
        WHEN MAX(LENGTH(elem.share_id)) > 20 THEN 0 
        ELSE 1 
    END AS validation
FROM tempTable
LATERAL VIEW EXPLODE(elements.elements) exploded AS elem
WHERE data = '2025-03-31'
GROUP BY agent

两个方法均能正确返回期望的validation结果，其中方法1更适合处理大数据量场景。

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