How can I query a Bigtable column value in BigQuery standard SQL?

Question

I'm trying to query a Bigtable column value with standard SQL in BigQuery:

#standardSQL
SELECT
  default.blah.cell.value
FROM
  mycategory.mytable
WHERE
  rowkey LIKE '123%'

I receive the error:

Error: Syntax error: Unexpected keyword DEFAULT at [3:3]

The above query works fine in legacy SQL, but not in standard SQL.

Selecting the rowkey only does work in #standardSQL:

#standardSQL
SELECT
  rowkey
FROM
  mycategory.mytable
WHERE
  rowkey LIKE '123%'

How can I select a column value in standard SQL, as the first query in this post is trying to do?

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-07-03T17:09:40

The problem is that DEFAULT is a reserved keyword, so you need to escape it using backticks. For example:

#standardSQL
SELECT
  `default`.blah.cell.value
FROM
  mycategory.mytable
WHERE
  rowkey LIKE '123%'

Edit: since cell is an array type field, you should use the ARRAY function if the goal is to read the values from it as an array:

#standardSQL
SELECT
  ARRAY(SELECT value FROM UNNEST(`default`.blah.cell)) AS values
FROM
  mycategory.mytable
WHERE
  rowkey LIKE '123%'

You can find more information about working with arrays in the documentation.