# Order of rows when using table and json\_to\_array

**URL:** <https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229>\
**Category:** Query Tuning\
**Created:** [November 16, 2022, 12:26pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229 "2022-11-16T12:26:18Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ahkjeldsen](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/a/eada6e/32.png) [@ahkjeldsen](https://www.singlestore.com/forum/u/ahkjeldsen)\
**Post date:** [November 16, 2022, 12:26pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/1 "2022-11-16T12:26:18Z")

</div>

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 ()`:

```auto
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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [November 17, 2022, 1:50am UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/2 "2022-11-17T01:50:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ahkjeldsen](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/a/eada6e/32.png) [@ahkjeldsen](https://www.singlestore.com/forum/u/ahkjeldsen)\
**Post date:** [November 17, 2022, 7:43am UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/3 "2022-11-17T07:43:01Z")

</div>

Thank you for replying 😊

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

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

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [November 18, 2022, 5:13pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/4 "2022-11-18T17:13:16Z")

</div>

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

Here is an example:

> [@Support Recursive Common Table Expressions (CTE)](https://www.singlestore.com/forum/t/support-recursive-common-table-expressions-cte/2326/3):
>
> The latest release candidate build of 8.0 supports recursive CTEs. See the announcement about the 8.0 RC. Feedback is appreciated.

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.

---

<div class="post-metadata">

**Author:** ![gtbenedict](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/g/b487fb/32.png) [@gtbenedict](https://www.singlestore.com/forum/u/gtbenedict)\
**Post date:** [September 21, 2023, 3:39pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/6 "2023-09-21T15:39:26Z")

</div>

Any updates on this feature request? We are really struggling with this issue. Or, is there and example of the TVF?  
Thanks,  
Tim

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [September 22, 2023, 11:06pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/7 "2023-09-22T23:06:58Z")

</div>

A Wasm TVF is still the best solution right now. We have not created an equivalent built-in. Still might do it at some point in the future.

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [September 22, 2023, 11:24pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/8 "2023-09-22T23:24:46Z")

</div>

Actually, some internal people at SingleStore solved this as part of a larger block of code by using this approach:

TABLE(our\_function(json\_array\_input))

In “our\_function” they used PSQL, not Wasm. They took the documents in the input json array, and put them in an enclosing document that also had a serial number property. Then the resulting docs were put back into an array and returned.

This was necessary because TABLE only outputs one column.

Their code is way too hairy to share here, because it is more general and solves more issues. But this approach can work.

---

<div class="post-metadata">

**Author:** ![gtbenedict](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/g/b487fb/32.png) [@gtbenedict](https://www.singlestore.com/forum/u/gtbenedict)\
**Post date:** [September 25, 2023, 6:57pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229/9 "2023-09-25T18:57:20Z")

</div>

Thanks Hanson. I’ll reach out to my sales rep on this. This issue is blocking us from moving forward with our evaluation.
