# How does TABLE(JSON\_TO\_ARRAY(Col)) work?

**URL:** <https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926>\
**Category:** Help\
**Created:** [April 19, 2021, 4:05pm UTC](https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926 "2021-04-19T16:05:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![prerak](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/p/90db22/32.png) [@prerak](https://www.singlestore.com/forum/u/prerak)\
**Post date:** [April 19, 2021, 4:05pm UTC](https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926/1 "2021-04-19T16:05:35Z")

</div>

I stumbled across the documentation for [TABLE](https://docs.singlestore.com/v7.3/reference/sql-reference/data-manipulation-language-dml/table/) and was fascinated by [Example 4](https://docs.singlestore.com/v7.3/reference/sql-reference/data-manipulation-language-dml/table/#example-4):

```
SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));

```

This seems to imply that `Role` column is readable in the JOIN clause. This appears to be similar to `LATERAL` keyword in Postgres, where the join table acts essentially as a sub-select. It does not look like SingleStore supports this in its general form.

For example, this does not work with any kind of join (error is `ER_BAD_FIELD_ERROR: Unknown column e.date in 'field list'`):

```
SELECT symbol, seq FROM ExecutionReport e JOIN Latest_Security(e.date); -- `Latest_Security` is a TVF

```

or

```
SELECT symbol, seq FROM ExecutionReport e JOIN (select symbol, name from Security where date=e.date) m ON e.symbol=m.symbol

```

In general, is there a way to refer to columns of a table in the “second table” of a JOIN clause, similar to the `LATERAL` keyword in Postgres? If not, 1) how does the `TABLE(JSON_TO_ARRAY(Col))` feature work? 2) any plans to add this general capability?

---

<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:** [April 19, 2021, 10:02pm UTC](https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926/2 "2021-04-19T22:02:45Z")

</div>

You are correct. We allow an implicit LATERAL with the TABLE function. We want to add LATERAL or some equivalent (like CROSS APPLY, or just having an implicit LATERAL) as a general-purpose operation at some point in the future but don’t have a committed date for that.

---

<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:** [April 19, 2021, 10:09pm UTC](https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926/3 "2021-04-19T22:09:50Z")

</div>

Feel free to open a feature request for this, and link to it from here. If there are others who want it, please up-vote it.

---

<div class="post-metadata">

**Author:** ![prerak](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/p/90db22/32.png) [@prerak](https://www.singlestore.com/forum/u/prerak)\
**Post date:** [April 20, 2021, 10:57am UTC](https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926/4 "2021-04-20T10:57:25Z")

</div>

Thank you, I was wondering if I was missing a magic keyword somewhere to make this work. Will open a feature request.
