Search code examples
sqlarraysstructhivehiveql

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.

Solution

  • 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)