Why is Right Join with an UNNest failing?

In my case i have a query that gets all possible menu items based on _type ‘menu_item’ then i want to join it with an UNNEST’d array which stores all id’s for a given user. The query works fine if its a left or regular join but when i want to right join it fails. Also what would be the best way to return a true or false if the item is part of UNNEST array ? i was thinking using the IFMISSINGORNULL, but how could i return a true if it is not missing ?

IFMISSINGORNULL(m._id, false) as enabled

here is my left join that i want to convert to right join

SELECT m._id,
       m.ame,
       m.parent,
       m.`level`,
       m.position,
       um.id,
       IFMISSINGORNULL(um.id, FALSE) AS enabled
FROM Contacts u
UNNEST u.menus AS um LEFT
    JOIN Contacts AS m ON m._type = "menu_item"
    AND um.id = m._id
WHERE u._type ="user_menu"
    AND um.hide = FALSE
    AND u.user_id = '8D6D24A5-D669-45DC-99AC-F257BDA133A4'

Try:

SELECT m._id,
       m.ame,
       m.parent,
       m.`level`,
       m.position,
       uu.umid,
       uu.umid IS NOT MISSING as enabled
FROM Contacts AS m RIGHT JOIN
    (SELECT um.id AS umid
     FROM Contacts u UNNEST u.menus AS um
     WHERE u._type ="user_menu"
       AND um.hide = FALSE
       AND u.user_id = '8D6D24A5-D669-45DC-99AC-F257BDA133A4') as uu
    ON m._type = "menu_item" AND uu.umid = m._id;

if you want a true/false to test missing just use IS NOT MISSING in the projection clause.

Joins are evaluated left-to-right, but you are joining with the result of an UNNEST, thus I used a subquery to perform the UNNEST operation first before the RIGHT JOIN.

Thanks but this does not resolve the issue …
So if i remove a key from the Doc that holds the User Menu it no longer is listed in the query which was not the desired result, i wanted to list all records for the Menu_item query and then join all from the user_menu and if there is no match / join which is on the id show the enabled as false.

Also in current query if there is an item in the user_menu query part but not in the menu_item it will still show in query which is also not desired

It seems to me you want the outer join to be the other way around. Try switching RIGHT JOIN to LEFT JOIN in the query above and see whether you get what you desired.

If i do a regular Join its the same result and a Left join fails as it complains about the Data Size beeing to big. It seems the query you provided does exactly the same as my original query which was a left join based.

So again here is waht i am looking for…

Get all records from menu_item and then join them with user_menu and if there is no match on the id i want a IS NOT MISSING as enabled

SELECT m._id,
       m.ame,
       m.parent,
       m.`level`,
       m.position,
       uu.umid,
       uu.umid IS NOT MISSING as enabled
FROM Contacts AS m LEFT JOIN
    (SELECT um.id AS umid
     FROM Contacts u UNNEST u.menus AS um
     WHERE u._type ="user_menu"
       AND um.hide = FALSE
       AND u.user_id = '8D6D24A5-D669-45DC-99AC-F257BDA133A4') as uu
    ON uu.umid = m._id
WHERE m._type = "menu_item";

if you know m._id is valid then you can also add to the WHERE clause: AND m._id IS NOT MISSING.

What you mentioned about Data Size being too big sounds like a different issue. Post server version and the exact error message you get.

Thanks this one works as expected…
I get all from my menu_item and the one missing in the user_menu show enabled false