BigQuery如何无需逐个处理键从JSON集合提取首个非空值

现有JSON集合结构如下：

需要从该集合中获取首个非null值，实现类似json-coalesce(my_collection,"$")的调用效果：

• 第一个用例返回5
• 第二个用例返回true
  要求实现过程不需要逐个单独处理每个键，求最优实现方案。

核心思路是通过JSON原生的全量递归遍历能力，跳过手动枚举键的步骤，直接过滤取第一个非null值即可，不同场景下的具体写法如下：

通用JSONPath实现（适配绝大多数JSON处理库） #

直接使用如下JSONPath表达式即可满足需求：

$..*[?(@ != null)][0]

表达式各段逻辑：

• $..*：从根节点开始递归遍历所有层级、所有键对应的值，完全不需要手动指定任何键名
• [?(@ != null)]：过滤掉所有值为null的节点，保留所有非null值组成结果集
• [0]：取结果集的第一个元素，也就是遍历顺序下的首个非null值，和预期用例的返回结果完全匹配。

关系型数据库JSON字段场景实现 #

如果是在MySQL 8.0+、PostgreSQL等支持JSON类型的数据库中操作，可以通过JSON展开函数实现，以MySQL为例：

SELECT j.val
FROM JSON_TABLE(
  JSON_EXTRACT(my_collection, '$**'),
  '$[*]' COLUMNS (val JSON PATH '$')
) j
WHERE j.val IS NOT NULL
LIMIT 1;

注意：部分低版本JSON处理库的递归遍历语法存在细微差异，比如少数老版本库用$**代替$..*做全量递归，核心逻辑保持一致即可。

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