CODEWITHSUNDEEP
sqlgoogle-bigquerygoogle-workspace
Ian Hyzy
Ian Hyzy

Reputation: 522

Querying JSON value in BigQuery

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?

Upvotes: 2

Views: 15911

Answers (2)

Vipul Pandey
Vipul Pandey

Reputation: 1738

though I am late but I am able to get via

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

Upvotes: 0

Scott B
Scott B

Reputation: 2944

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:

enter image description here

Upvotes: 6

Related Questions