BigQuery :过滤重复字段和普通字段

Question

我有下表：

row | cust_id | name
1   | 34      | word1 word2 someworkd  
    |         | something word1
    |         | blabla
2   | 35      | word1 word2 word3
    |         | word4 word5

select * result JSON

[
  {
    "cust_id": "34",
    "name": [
      "word1 word2 someworkd",
      "something word1",
      "blabla"
    ]
  },
  {
    "cust_id": "35",
    "name": [
      "word1 word2 word3",
      "word4 word5",
      "word1word5"
    ]
  }
]

模式JSON：

[
  {
    "mode": "NULLABLE",
    "name": "cust_id",
    "type": "STRING"

  },
  {
    "mode": "REPEATED",
    "name": "name",
    "type": "STRING"
  }
]

我想根据cust_id和重复字段的值进行过滤，所以查询如下：

SELECT * FROM `mytable` WHERE  cust_id='34' and name like'%word1%'

查询的预期输出：

row | cust_id | name
1   | 34      | word1 word2 someworkd  
    |         | something word1

JSON格式查询的预期输出：

  {
    "cust_id": "34",
    "name": [
      "word1 word2 someworkd",
      "something word1"
    ]
  }
]

受这个问题BigQuery : filter repeated fields with standard SQL的启发，我尝试了这个查询：

SELECT cust_id, name   FROM `mytable`, UNNEST(name) AS name WITH OFFSET o
WHERE name like '%word1%' and cust_id='34'

它输出以下内容：

row | cust_id | name
1   | 34      | word1 word2 someworkd  
    | 34      | something word1

输出JSON：

[
  {
    "cust_id": "34",
    "name": "word1 word2 someworkd"
  },
  {
    "cust_id": "34",
    "name": "something word1"
  }
]

输出结果与我想要的不一样，请问如何实现？

Answer 1

我会发布我所做的解决方案，这是我需要的，也许对其他人有用

SELECT
  cust_id,
  ARRAY(
  SELECT
    name
  FROM
    `mytable`,
    UNNEST(name) AS name
  WHERE
    name LIKE '%word1%'
    AND cust_id='34') AS name
FROM
  `mytable`
WHERE
  cust_id="34"

结果：

row | cust_id | name
1   | 34      | word1 word2 someworkd  
    |         | something word1

JSON：

[
  {
    "cust_id": "34",
    "name": [
      "word1 word2 someworkd",
      "something word1"
    ]
  }
]

Answer 2

下面是针对BigQuery标准SQL的说明

#standardSQL
SELECT * REPLACE(
    ARRAY(SELECT * FROM t.name WHERE name LIKE'%word1%') 
  AS name)
FROM `project.dataset.table` t
WHERE cust_id = 34