Can you have multiple lateral cross joins in postgresql?

Question

Is it possible to have multiple lateral cross joins in one query in PostgreSQL?

The problem I am facing is I am cross joining a json_array_element to some UUID values as below,

 CROSS JOIN LATERAL json_array_elements_text(dep."JSON_ID" -> 'staffdepartmentId') AS j(data)
 WHERE j.data :: uuid NOT IN (  '1b381940-1df1-4ced-8b66-baad6e8516ee',
                                'c690d00c-9a57-11ec-b909-0242ac120002',
                                'c690ddf4-9a57-11ec-b909-0242ac120002',
                                '404429ae-ec74-42c0-8e25-0f23b5f88a11',
                                'fe701d28-377b-450e-8526-f949b9df0eb5'
                             )

So, I need to do another cross join as above if possible,

 CROSS JOIN LATERAL json_array_elements_text(dep."JSON_ID" -> 'studentdepartmentId') AS s(data)
 WHERE s.data :: uuid NOT IN  
                 (select st.id 
                       from student.records st
                        where st.id
                        in ( 'b8ba080a-7fb3-4638-af94-d177b73e44f4,
                             '0ff8bc79-6a1a-4669-a4c2-8504f3879ca2',
                             'e3447829-0b4d-4938-a373-f6669747f24b',
                             '6500b90b-a652-4f2e-a248-d57db238c0e9'
                             ))

Obviously I cannot use two where clause in the same query. But I am not sure how to do the second cross join query without a cross join lateral. Any help is highly appreciated

Answer 1

You may be able to solve your problem by breaking out one of the queries (or both) into a CTE and adding an index column. Then join the data together as needed on the index. Each CTE would certainly be able to have it's own lateral cross join.

Example:

with cte1 as (
    <insert query1 here>
)
, cte2 as (
    <insert query2 here>
)

select *
from table1
left join cte1 on cte1.index = table1.index
left join cte2 on cte2.index = table1.index