CODEWITHSUNDEEP
google-bigquery
Andrey Fedorov
Andrey Fedorov

Reputation: 67

Why does implicit UNNEST not work as the docs say it should?

According to the BigQuery docs:

In implicit unnesting, array_path must resolve to an ARRAY and the UNNEST keyword is optional.

Example:

SELECT * FROM mytable.struct_typed_column.array_typed_field1;

Yet when I try this on a real appengine log exported via StackDriver:

SELECT *
FROM log_stream.appengine_googleapis_com_request_log_20160928.protoPayload.line

I get the error:

Invalid table name: log_stream.appengine_googleapis_com_request_log_20160928:protoPayload.line

To clarify what I am trying to accomplish, in case that helps, this query returns rows that I'm trying to UNNEST:

SELECT protoPayload.line
FROM log_stream.appengine_googleapis_com_request_log_20160928
WHERE ARRAY_LENGTH(protoPayload.line) > 0
LIMIT 10

What am I missing?

Upvotes: 0

Views: 241

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

I filed an (internal) bug against our documentation to clarify this section based on your feedback so hopefully we can have it fixed soon. This section of the migration guide is a little more helpful in the meantime.

Assuming that line repeats this does what you want:

SELECT line
FROM log_stream.appengine_googleapis_com_request_log_20160928 AS t,
  t.protoPayload.line AS line
LIMIT 10;

Or, if the protoPayload repeats, this does:

SELECT protoPayload.line
FROM log_stream.appengine_googleapis_com_request_log_20160928 AS t,
  t.protoPayload AS protoPayload
LIMIT 10;

Upvotes: 1

Related Questions