Use specific array column in the where clause

Having a function that returns an array and in a query we use the function to return results such as:

{"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"}
{"TUNDUMA RETURN (BORDER)","DEPARTURE DATE",29/01/2016,2,"ON THE ROAD"}

How can we add a WHERE clause that will filter by the 5th column of the array searching for the ON THE ROAD values only?

Is there a way to reference array[4]?

Tried with

where trip_status(tlid) <@ array['AT CHECKPOINT']::varchar

with trip_status() being the function that returns the array but am getting the following error:

Query failed: ERROR: operator does not exist: character varying[] <@ character varying 
LINE 6: ...ere trip_closed(tlid)=false and trip_status(tlid) <@ array[ ... ^ 
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Does something like where trip_status(tlid)[4] = 'AT CHECKPOINT' exist?

You put the cast outside the array constructor - use ::varchar[] instead. The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle.

But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to use those in a WHERE clause, for example. Then construct your array directly before returning it to whatever that needs it.

StackExchange Database Administrators Q#127832, answer score: 7