sql - 检查记录字段是否包含使用标准 sql (Google BigQuery) 的值

Question

例如，如果我有这样的架构:

[{
    "name": "fullName",
    "type": "string",
    "mode": "required"
 },
 {
    "name": "children",
    "type": "record",
    "mode": "repeated",
    "fields": [
    {
        "name": "name",
        "type": "string",
        "mode": "nullable"
    },
    {
        "name": "gender",
        "type": "string",
        "mode": "nullable"
    },
    {
        "name": "age",
        "type": "integer",
        "mode": "nullable"
    }] 
}]

一个人可以有多个 child ， child 存储在重复记录中，可以通过children.name、children.gender和children.age访问。
如果此人有多个 child ，则可能会为该人显示多行，但所有这些行共享一个行号。

我想从该表中选择所有行，其中一行中的一个 child 的名字与某个值匹配，我认为它可能类似于 children.name 包含的内容。

Answer 1

I read your question as - How to select all parents who have child with specific name?

#standardSQL
SELECT *
FROM yourTable
WHERE 'child1' IN (SELECT name FROM UNNEST(children))

您可以使用以下虚拟数据进行测试

#standardSQL
WITH yourTable AS (
  SELECT 
    'parentA' AS fullName, 
    [STRUCT<name STRING, gender STRING, age INT64>
      ('child1', 'm', 10), ('child2', 'f', 11), ('child3', 'f', 8)
    ] AS children UNION ALL
  SELECT 'parentB', 
    [STRUCT<name STRING, gender STRING, age INT64>
      ('child4', 'm', 10), ('child5', 'f', 11), ('child6', 'f', 8)] UNION ALL
  SELECT 'parentC', 
    [STRUCT<name STRING, gender STRING, age INT64>
      ('child7', 'm', 10), ('child1', 'f', 11), ('child8', 'f', 8)] 
)
SELECT *
FROM yourTable
WHERE 'child1' IN (SELECT name FROM UNNEST(children))