如何修改BigQuery查询从JSON数组提取value属性用于IN子查询

SELECT JSON_EXTRACT_SCALAR(value_obj, '$.value') AS extracted_value
FROM UNNEST(JSON_EXTRACT_ARRAY(
  JSON '[{"active":true,"key":"key1","values":[{"active":true,"value":"value1"}]},{"active":true,"key":"key2","values":[{"active":true,"value":"value2"}]}]'
)) AS tag,
UNNEST(JSON_EXTRACT_ARRAY(tag, '$.values')) AS value_obj

• 砍掉原查询里多余的嵌套子查询，直接用交叉UNNEST同时展开顶层JSON数组和每个tag对象里的values子数组
• 用JSON_EXTRACT_SCALAR替代原查询的JSON提取函数，直接取出value字段的字符串值，而非完整的JSON对象
• 最终输出单列extracted_value，包含"value1"、"value2"两个值，可直接作为IN表达式的子查询使用，示例如下：

SELECT *
FROM your_target_table
WHERE target_column IN (
  SELECT JSON_EXTRACT_SCALAR(value_obj, '$.value') AS extracted_value
  FROM UNNEST(JSON_EXTRACT_ARRAY(
    JSON '[{"active":true,"key":"key1","values":[{"active":true,"value":"value1"}]},{"active":true,"key":"key2","values":[{"active":true,"value":"value2"}]}]'
  )) AS tag,
  UNNEST(JSON_EXTRACT_ARRAY(tag, '$.values')) AS value_obj
)

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