4

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?

Anthony Kong
  • 141
  • 4

2 Answers2

2

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.

0

You can use something like

SELECT * 
FROM gae_log.appengine_googleapis_com_request_log_20180323,  
     UNNEST(protoPayload.line) as line
where 
     line.logMessage  like '%14994838999176%'
LIMIT 1000

Basically, you need to use UNNEST to query an ARRAY in BigQuery.

mustaccio
  • 28,207
  • 24
  • 60
  • 76
J Roy
  • 1