在Presto中筛选数组内存在指定JSON字段的行并避免报错

我有一个字段jsonCol，存储的是JSON对象数组，示例数据如下：
第一行数据：

[{'name': 'fieldA', 'enum': 'someValA'},
 {'name': 'fieldB', 'enum': 'someValB'},
 {'name': 'fieldC', 'enum': 'someValC'}]

第二行数据：

[{'name': 'fieldA', 'enum': 'someValA'},
 {'name': 'fieldC', 'enum': 'someValC'}]

我需要筛选出包含fieldB且其enum值为someValB的行，但当前查询在fieldB不存在时会报错：

Error running query: Array subscript must be less than or equal to array length: 1 > 0

当前使用的查询语句：

SELECT
    json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum') AS myField
FROM myTable
WHERE
    json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum') = 'someValB'

请问如何实现既检查someValB的值，又能忽略fieldB不存在的情况？

报错根源是：当fieldB不存在时，filter返回空数组，直接用[1]访问下标会触发数组越界（数组下标从0开始，空数组长度为0，无法访问下标1）。以下三种方法可以解决这个问题：

方法1：先判断数组长度再处理 #

先提取匹配fieldB的数组，用cardinality()函数判断数组长度，过滤掉空数组的行后再取值，避免越界：

SELECT
    json_extract_scalar(filtered_b[1], '$.enum') AS myField
FROM myTable
CROSS JOIN UNNEST([cast(json_parse(jsonCol) AS array(json))]) AS t(arr)
CROSS JOIN UNNEST([filter(arr, x -> json_extract_scalar(x, '$.name') = 'fieldB')]) AS t(filtered_b)
WHERE
    cardinality(filtered_b) > 0
    AND json_extract_scalar(filtered_b[1], '$.enum') = 'someValB'

这种方式把重复的filter计算提取出来，既避免了下标越界，也提升了查询效率。

方法2：用try()函数捕获错误 #

如果你的SQL环境支持try()函数，可以用它包裹下标访问逻辑，出错时返回NULL，再通过WHERE条件排除这些行：

SELECT
    try(json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum')) AS myField
FROM myTable
WHERE
    try(json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum')) = 'someValB'

try()会自动捕获下标越界错误并返回NULL，WHERE条件会直接排除这些NULL行。

方法3：用exists判断元素存在性 #

最简洁的方式是通过UNNEST展开数组，用exists子查询判断是否存在符合条件的元素，彻底避开数组下标操作：

SELECT
    json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum') AS myField
FROM myTable
WHERE
    exists (
        SELECT 1
        FROM UNNEST(cast(json_parse(jsonCol) AS array(json))) AS x
        WHERE json_extract_scalar(x, '$.name') = 'fieldB'
          AND json_extract_scalar(x, '$.enum') = 'someValB'
    )

这种方式逻辑更直观，直接检查数组中是否存在满足条件的元素，从根源上避免了下标越界问题。

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