Why is Right Join with an UNNest failing?

SQL++
2

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.