How to query for certain text in log messages from Google App Engine that are exported to BigQuery?

Question

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

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

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?

Answer 1

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:

SELECT x
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.