Querying JSON value in BigQuery

Question

I have a BigQuery table ("activity") where one column ("groups") contains a JSON value (the table is an export of Google Workspace logs).

I'd like to be able to select rows from the table based on the group_email JSON value in the group column. I checked the docs here, but my queries always return null. I tried:

SELECT
  record_type,
  email,
  JSON_VALUE('groups.group_email') AS group_email

but got null. I also tried using the column index of group_email

SELECT
  record_type,
  email,
  JSON_VALUE('groups[4]') AS group_email

but no luck. I tried these same combinations as part of the WHERE statement but got the same results:

SELECT
  *
FROM
  `company.workspace_prod.activity`
WHERE
  record_type = 'groups'
  and
  JSON_VALUE('groups.group_email') = '[email protected]'
LIMIT
  10

I also saw this answer but using JSON_EXTRACT_SCALAR('groups', "$.groups[0].group_email") AS group_email also returns null

Any idea what I'm doing wrong?

Answer 1

You may try below approach.

SELECT
  record_type,
  email,
  JSON_VALUE(groups,'$.group_email') AS group_email
FROM
  `company.workspace_prod.activity`

You may refer to this JSON function Documentation for more details.

Please see screenshot of my testing below using the correct syntax for JSON_VALUE.

SAMPLE DATA:

int64_field_0	string_field_1
1	20
2	"This is a string"
3	{"id": 10, "name": "Alice"}

QUERY RESULT: