How does TABLE(JSON_TO_ARRAY(Col)) work?

Help
1

I stumbled across the documentation for TABLE and was fascinated by 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?

2

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.

3

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.

4

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