CODEWITHSUNDEEP
google-bigquery
FcoZ
FcoZ

Reputation: 157

How can I get the last element of an array? SQL Bigquery

I'm working on building a follow-network form Github's available data on Google BigQuery, e.g.: https://bigquery.cloud.google.com/table/githubarchive:day.20210606

The key data is contained in the "payload" field, STRING type. I managed to unnest the data contained in that field and convert it to an array, but how can I get the last element?

Here is what I have so far...

select type, 
  array(select trim(val) from unnest(split(trim(payload, '[]'))) val) payload
from `githubarchive.day.20210606` 
where type = 'MemberEvent'

Which outputs:

enter image description here

How can I get only the last element, "Action":"added"} ? I know that

select array_reverse(your_array)[offset(0)]

should do the trick, however I'm unsure how to combine that in my code. I've been trying different options without success, for example:

with  payload as ( select  array(select trim(val) from unnest(split(trim(payload, '[]'))) val) payload from `githubarchive.day.20210606`)

select type, ARRAY_REVERSE(payload)[ORDINAL(1)]

from `githubarchive.day.20210606` where type = 'MemberEvent'

The desired output should look like:

enter image description here

Upvotes: 5

Views: 16863

Answers (3)

jaal
jaal

Reputation: 1

Ankit Kumar's solution is preferable, but a closing square bracket is missing.

-> 'SELECT event[OFFSET(ARRAY_LENGTH(event)-1)]'

Upvotes: -1

Ankit Kumar
Ankit Kumar

Reputation: 51

There is a solution without reversing the array.

SELECT event[OFFSET(ARRAY_LENGTH(event)-1)

Upvotes: 4

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

To get last element in array you can use below approach

select array_reverse(your_array)[offset(0)]

I'm unsure how to combine that in my code

select  type, array_reverse(array(
    select trim(val) 
      from unnest(split(trim(payload, '[]'))) val
  ))[offset(0)] 
from `githubarchive.day.20210606`
where type = 'MemberEvent'

Upvotes: 11

Related Questions