Order of rows when using table and json_to_array

Help Query Tuning
1

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?

2

TABLE function output is unordered. We have a feature request open for a function like TABLE_EX that will have a serial number column in the output. No ETA for that. Maybe next year.

Your ROW_NUMBERS will get layered on to the result of the join, after the fact, in your example.

If you really need this, you could implement a Wasm TVF that includes a serial number column with the data from each array element.

3

Thank you for replying :blush:

Would really appreciate that table_ex function. Do you have a place for feature requests where customers can vote? :grinning_face_with_smiling_eyes:

Do you know if there will be a noticeable performance hit when using such a TVF as you suggest?

4

Sure, you can open a topic in the Feedback channel requesting a feature, and people can vote it up.

Here is an example:

And the performance of TABLE_EX should be similar to TABLE, if we do it. It’ll take a little longer due to having to output more columns.