I have used the 'Create Export' in the Logging section of Google Cloud Platform to export log entries into Big Query for analysis

enter image description here

Here is part of the structure of the table in BigQuery that logs the log entry

enter image description here

I want to search for entries that contains this user id 14994838999176.

In BigQuery, I issue the following query (in standard SQL dialect),

SELECT * FROM gae_log.appengine_googleapis_com_request_log_20180323

where protoPayload.line.logMessage  like '%14994838999176%'

LIMIT 1000 

It failed with the error message:

 Query Failed

 Error: Cannot access field logMessage on a value with type 
 ARRAY<STRUCT<time TIMESTAMP, severity STRING, logMessage STRING, ...>> at [3:25]

How can I correct my sql statement?


The reason why this query failed is because you have a table with nested repeated field protoPayload.line (datatype RECORD).

You can try implicit unnesting, it would be something like:

FROM `gae_log.appengine_googleapis_com_request_log_20180323` as t, 
      t.protoPayload.line AS x
WHERE x.logMessage  like '%14994838999176%'

You can also have a look on how to query STRUCT elements in an ARRAY here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.