Hi there.
I stumbled upon something that is behaving differently than what I would expect.
I have a column containing a json array, which I at times need to join with other data. I need to be able to get the index (or position) of the array element.
When only using table
and json_to_array
, I can compute the index using row_number() over ()
:
SELECT x.table_col,
ROW_NUMBER() OVER () index_in_array
FROM TABLE(JSON_TO_ARRAY('["a", "b", "c"]')) x;
+-----------+----------------+
| table_col | index_in_array |
+-----------+----------------+
| "a" | 1 |
| "b" | 2 |
| "c" | 3 |
+-----------+----------------+
But when I join with a table, the order is reversed:
SELECT t.greeting,
x.table_col,
ROW_NUMBER() OVER (PARTITION BY t.greeting) index_in_array
FROM (SELECT 'hello' greeting FROM dual
union all
SELECT 'world' greeting FROM dual
) t
JOIN TABLE(JSON_TO_ARRAY('["a", "b", "c"]')) x;
+----------+-----------+----------------+
| greeting | table_col | index_in_array |
+----------+-----------+----------------+
| hello | "c" | 1 |
| hello | "b" | 2 |
| hello | "a" | 3 |
| world | "c" | 1 |
| world | "b" | 2 |
| world | "a" | 3 |
+----------+-----------+----------------+
I would expect to see 1 for "a"
, 2 for "b"
and 3 for "c"
.
Is this expected behavior? If so, how do I reliably get the position of the element in the array?