BigQuery:将重复字段与普通字段一起过滤

Question

我有下表：

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

选择*结果JSON

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

schema 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:用标准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

以下是 BigQuery Standard SQL

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

Answer 2

我将发布我所做的解决方案，该解决方案根据需要工作，可能对其他人有用

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"
    ]
  }
]