Support UNNEST

[therapon]

Alternate syntax for Unnesting Nested Collections

In this section, we simply present an alternate way to express and think
about unnesting collections. In the spirit of some prior query
languages, PartiQL also allows you to optionally use the keyword
UNNEST in the FROM clause. The two queries below have identical
meaning, regardless of whether UNNEST is used or not, assuming there
is no schema e with table projects.

SELECT e.name AS employeeName,    
       p.name AS projectName                    
FROM hr.employeesNest AS e,           
     e.projects AS p                                        
WHERE p.name LIKE '%security%'        

equivalent to

  SELECT e.name AS employeeName,   
             p.name AS projectName     
           FROM hr.employeesNest AS e,      
            UNNEST(e.projects) AS p    
       WHERE p.name LIKE '%security%'   

In the same spirit, one may think that the FROM clause of the
example executes, in a sense, a JOIN between employees and projects.
Except that unlike a conventional SQL join that would require an ON
condition, the employees-projects join condition is implicit in the
nesting of the projects data into the employee data. If it helps you to
think in terms of JOIN, you may replace the comma with JOIN.
That is, the following two queries are equivalent.

SELECT e.name AS employeeName, 
       p.name AS projectName   
FROM hr.employeesNest AS e,    
     e.projects AS p           
WHERE p.name LIKE '%security%' 

equivalent to

SELECT e.name AS employeeName,   
       p.name AS projectName     
FROM hr.employeesNest AS e JOIN  
    e.projects AS p              
WHERE p.name LIKE '%security%'   

And you can write both JOIN and UNNEST as well

SELECT e.name AS employeeName,    
       p.name AS projectName      
FROM hr.employeesNest AS e,       
     e.projects AS p              
WHERE p.name LIKE '%security%'    

equivalent to

SELECT e.name AS employeeName,    
       p.name AS projectName      
FROM hr.employeesNest AS e JOIN   
     UNNEST(e.projects) AS p      
WHERE p.name LIKE '%security%'