CODEWITHSUNDEEP
google-bigquery
user2362699
user2362699

Reputation: 646

BigQuery Subqueries

I am new to BQ. My objective is to create a query which will drill down to specific values.

The following query displays iam roles, such as ServiceAccountUser or serviceAccountTokenCreator.

SELECT name, iam.role AS roles, (ARRAY_TO_STRING(iam.members, "")) AS members
  FROM dataset_blah_7553a5b37_Project,
  UNNEST (iam_policy.bindings) AS iam
  GROUP by name, roles, members
  HAVING role = 'roles/iam.serviceAccountUser' OR role = 'roles/iam.serviceAccountTokenCreator'

It displays for example the following:

Row            name                                          roles                                 members
1 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountUser                 serviceAccount:[email protected]
2 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountUser                 user:  [email protected]
3 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountTokenCreator         group: [email protected]
4 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountUser                 user:  [email protected]
5 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountUser                 serviceAccount:[email protected]
6 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountUser                 serviceAccount:[email protected]
7 //cloudresourcemanager.googleapis.com/projects/1234567890  roles/iam.ServiceAccountUser                 serviceAccount:[email protected]

However, I need to drill down further to filter only "group:" and "user:", such as

group: [email protected]
user:  [email protected]

How can I accomplish it? Subqueries?

Upvotes: 0

Views: 88

Answers (1)

Sakshi Gatyan
Sakshi Gatyan

Reputation: 2126

Yes, we can do filtering using subqueries in bigquery. [1]

You can try something like

SELECT table1.name, table1.roles, table1.members
FROM(SELECT name, iam.role AS roles, (ARRAY_TO_STRING(iam.members, "")) AS members
     FROM dataset_blah_7553a5b37_Project,
     UNNEST (iam_policy.bindings) AS iam
     GROUP by name, roles, members
     HAVING role = 'roles/iam.serviceAccountUser' OR role = 
     'roles/iam.serviceAccountTokenCreator') AS table1 
WHERE table1.members= "group: [email protected]" AND table1.members="user:  [email protected]"

[1]https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#table_subquery_concepts

Upvotes: 1

Related Questions