Hive SQL: Accessing Last Element of a Json array

Question

I have a json column called "schedule_info" with a format like the following:

{schedule: [{"date":"2020-01-01"},{"date":"2021-01-01"},{"date":"2022-01-01"},{"date":"2022-02-01"},{"date":"2022-03-01"}]}

I'm able to access the first element easily with:

get_json_object(schedule_info, '$.schedule[0].date') as first_date.

However, When I try to access the last date, I'm not able to..

I've tried "get_json_object(schedule_info, '$.schedule[-1].date')". But, this just returns the whole schedule array. I've also tried including the array length in the brackets, which didn't work as well.. Is there an easy way to do this?

I want to easily parse out data from the last element of a json array without having to explode the array.

Answer 1

if you use brickhouse, json_split could help you like this:

select 
json_split(get_json_object(schedule_info, '$.schedule'))[
size(json_split(get_json_object(schedule_info, '$. schedule')))-1
] as last_date
from your_table

here you use json_split twice, one for get array element, another for get array size

or use reverse to eliminate one call to json_split

select 
reverse(json_split(get_json_object(schedule_info, '$.schedule')))[0] as last_date
from your_table

json_split blog

brickhouse github