BigQuery : filter repeated fields together with normal field

Question

I have the following table :

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

schema JSON:

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

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

I want to filter by cust_id and repeated field on their value so with query like:

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

Expected output from query:

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

Expected output from query in JSON format:

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

Inspired by this question BigQuery : filter repeated fields with standard SQL I try this query :

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

Which outputs this:

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

Output JSON:

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

Output result is not the same that I would like to have, how can I achieve this please ?

dave101ua dave101ua · Accepted Answer · 2020-09-15T12:36:30

I will post solution I did which works as I need, maybe useful for others

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"

Result:

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 together with normal field

2 Answers