Can't query one record in BigQuery table, but can query others

Question

I export Google Workspace logs to BigQuery. There are a small number of top-level records and then many nested groups of records. I can query the top level of records and most sub-levels fine but I can't select the groups records. select group_id,admin.user_email,admin.group_email works fine, for example.

But when I try to run a very similar query on the Groups records it fails with Syntax error: Expected end of input but got keyword GROUPS

SELECT
  group_id,
  groups.group_email
FROM
  `workspace-analytics.workspace_prod.activity`
WHERE
  groups.group_email='group@domain.com'
LIMIT
  100;

What am I doing wrong? Why does this record in particular refuse to work the way the others do?

Answer 1

Answer from @MatBailie, posting it as a WikiAnswer:

The error message tells you that GROUPS is a keyword. If you quote it, then bigquery will realise its a reference and not a keyword. groups.group_email.

Because admin isn't a keyword. Imagine you had a column named from, you couldn't do SELECT from FROM table without confusing the shit out of the parser, but SELECT from FROM table isn't ambiguous at all. You can CHOOSE to quote all references regardless, but if they're keywords then they MUST be quoted.

Make sure you're quoting using backticks, the same ones you use in dataset names.