google-bigquery - 获取所有重复记录满足条件的嵌套数据集

Question

我在 BigQuery 表中有以下结构：

make        STRING    NOT NULLABLE
feature     RECORD    REPEATED
  name      STRING    REQUIRED

以下查询返回至少一条feature记录满足条件的所有数据length(f.name) = 5：

select * from tbl t, t.feature tf
where length(tf.name) = 5

如何仅获取所有feature.name记录都满足上述条件的数据？

标签： google-bigquery

Answer 1

尝试子查询：

select * from tbl t
where not exists (
    select 1
    from unnest(t.feature) AS tf
    where length(tf.name) != 5
)