Search code examples
presto

How do I get the index (position) of an element from a json array in presto?

I'm using Presto, and have two columns.

Column A is a json array which looks like ["apple", "banana", "cherry"]

Column B is a string value "cherry"

All I want to do is look up Column B in the array of Column A, and get the index if they find a match.

I can't find a function to do this easily in Presto documentation anywhere.

I tried json_array_find, position_at, element_at, and different combinations of these functions, but they all either aren't compatible with presto, or return 0 or null.

Solution

  • You can use the element_at function along with the array_position function.

    SELECT array_position(CAST(json_parse(column_a) AS array<varchar>), column_b) - 1 AS match_index
FROM your_table;

    This query should return the index (0-based) if a match is found, and -1 if there is no match.