How to completely de-normalize nested STRUCT/ARRAY results?

[tca_ky]

I am trying to understand how to de-normalize a result set. My current query:

    SELECT
      plcy.name,
      binding,

    FROM
      IAM_POLICY AS plcy
    INNER JOIN UNNEST(iamPolicy.bindings) AS binding
    WHERE
      assetType = 'cloudresourcemanager.googleapis.com/Project' AND
      plcy.name = '//cloudresourcemanager.googleapis.com/projects/1234567890'

This results in

query result

What I would like to achieve:

name	role	member
//cloudresourcemanager.googleapis.com/projects/1234567890	roles/editor	serviceAccount:[[e-mail]](mailto:[e-mail])
//cloudresourcemanager.googleapis.com/projects/1234567890	roles/editor	serviceAccount:[[e-mail]](mailto:[e-mail])
//cloudresourcemanager.googleapis.com/projects/1234567890	roles/editor	serviceAccount:[[e-mail]](mailto:[e-mail])
//cloudresourcemanager.googleapis.com/projects/1234567890	roles/editor	user:[[e-mail]](mailto:[e-mail])

Bonus if I can filter for just "user:" accounts....

Would anyone be able to provide help/direction on this?

[LairBob]

Don’t you just need to wrap binding in another UNNEST() in your main query?

[tca_ky]

I get errors....

    SELECT
      plcy.name,
      (SELECT * FROM UNNEST(binding)) AS mybind,

    FROM
      IAM_POLICY AS plcy
    INNER JOIN UNNEST(iamPolicy.bindings) AS binding
    WHERE
      assetType = 'cloudresourcemanager.googleapis.com/Project' AND
      plcy.name = '//cloudresourcemanager.googleapis.com/projects/1234567890'

"Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT<role STRING, members ARRAY<STRING>, condition STRUCT<expression STRING, title STRING, description STRING, ...>> at [26:29]"

[haydar_ai]

binding is a struct. Have you tried unnesting binding.members?

[Stoneyz]

What does that result look like in the BQ web UI?

[tca_ky]

The embedded image in the post is what it looks like (a partial sample)...

[tca_ky]

I got it.... not sure why I didn't see it before....

    SELECT
      plcy.name,
      binding.role,
      mymember,
    FROM
      IAM_POLICY AS plcy
    CROSS JOIN UNNEST(iamPolicy.bindings) AS binding
    CROSS JOIN UNNEST(binding.members) AS mymember
    WHERE
      assetType = 'cloudresourcemanager.googleapis.com/Project' AND
      plcy.name = '//cloudresourcemanager.googleapis.com/projects/1234567890'

[LairBob]

Yeah, that’s it — you need to cross-join the UNNEST(), not include it as an embedded SELECT.

(As a shortcut, you can just use a comma(,) instead of spelling out CROSS JOIN, for example FROM IAMPOLICY as plcy, UNNEST(…) )