CODEWITHSUNDEEP
sqlarraysstructhivehiveql
sidhartha swain
sidhartha swain

Reputation: 43

Finding the First & Last of Array struct

Having an array struct in file like below

[{"A":"1","B":"2","C":"3"},{"A":"4","B":"5","C":"6"},{"A":"7","B":"8","C":"9"}]

How can I get the first & last value of column "A" ("1","7")

Need to write in Hive SQL.

Thanks in advance.

Upvotes: 4

Views: 6137

Answers (1)

leftjoin
leftjoin

Reputation: 38290

first element of array is array_name[0], last is array_name[size(array_name)-1].

Demo:

 select example_data[0].A, example_data[size(example_data)-1].A
   from
   ( --Your example data
   select array(named_struct("A","1","B","2","C","3"),named_struct("A","4","B","5","C","6"),named_struct("A","7","B","8","C","9")) as example_data
   )s;
OK
1       7
Time taken: 2.72 seconds, Fetched: 1 row(s)

Upvotes: 6

Related Questions