Hello dear reader
I am trying to do a join based on a nested property of the target
SELECT usr as TheUser, fac AS Facilities
Facilities fac ON ARRAY_CONTAINS(OBJECT_NAMES(fac.registry.associates), usr.userUUId)
WHERE META(usr).id == ‘User_gmail@gmail.com’ AND META(fac).id LIKE ‘Facility_%’
This query is faulted
Is something like this possible? Or perphaps is there another way to approach the issue?
I tried using a let clause with a subquery however then i am facing an issue of use keys in a corelated subquery ( to my understanding - usr.userUUId makes it dependent) and i cannot provide for those keys…
I also thought about reversing the order - i.e first selecting the facilities but even if i already had the userUUId property value (which i dont, the key is the email address) wouldnt this cause the subquery for the user join to run each time a facility is selected? the query builds a kind of a user “portfolio” for a single target (LIMIT 1…)
Thanks in advance!