bigquery concat array of bool values

0
votes

Trying to run this query and having an issue with the "granted" field:

SELECT 

ARRAY_TO_STRING(ARRAY(SELECT permission FROM t.protopayload_auditlog.authorizationInfo), ',') permissions,
ARRAY_TO_STRING(ARRAY(SELECT granted FROM t.protopayload_auditlog.authorizationInfo), ',') granted,
ARRAY_TO_STRING(ARRAY(SELECT resource FROM t.protopayload_auditlog.authorizationInfo), ',') resource,

protopayload_auditlog.requestMetadata.callerSuppliedUserAgent AS agent,
timestamp,
severity,
resource.labels.bucket_name as name_of_bucket,
protopayload_auditlog.authenticationInfo.principalEmail

FROM 
`mytable` t

Granted is boolean not string and im getting this error:

No matching signature for function ARRAY_TO_STRING for argument types: ARRAY, STRING. Supported signatures: ARRAY_TO_STRING(ARRAY, STRING, [STRING]); ARRAY_TO_STRING(ARRAY, BYTES, [BYTES]) at [6:1]
1
google-bigquery

1 Answers

2
votes

You should CAST Boolean to STRING as in below example 

ARRAY_TO_STRING(ARRAY(SELECT CAST(granted AS STRING) FROM t.protopayload_auditlog.authorizationInfo), ',') granted,