CODEWITHSUNDEEP
google-bigquery
Lior
Lior

Reputation: 1457

BigQuery position function

I'm trying to use the position function and it doesn't seem to work as expected:

POSITION(field) - Returns the one-based, sequential position of field within a set of repeated fields.

I expect the position to give the original position in the data loaded, while I get the position in the result set (i.e - after the where clause ran).

Is there a way to get the position in the original data loaded?

Compare the two queries below to see the issue.

SELECT  
  ngram,
  cell.value,
  position(cell.volume_count) as pos,
FROM [publicdata:samples.trigrams] 
where ngram contains ", says Blake"

SELECT  
  ngram,
  cell.value,
  position(cell.volume_count) as pos,
FROM [publicdata:samples.trigrams] 
where ngram contains ", says Blake" and integer(cell.value) % 5 = 0

Upvotes: 3

Views: 1328

Answers (1)

Jordan Tigani
Jordan Tigani

Reputation: 26617

The issue is that the position function gets evaluated after the where clause filters the nested values. You can fix this with a nested select:

select
  ngram,
  value,
  pos,
from (
  select  
    ngram,
    cell.value as value,
    position(cell.volume_count) as pos,
  from [publicdata:samples.trigrams] 
  where ngram contains ", says Blake"
)
where integer(cell.value) % 5 == 0

Upvotes: 2

Related Questions