I am wondering if and how i can join an Array in a doc with other docs. In my case i have a doc that stores all ID’s of menu’s a user has permission to so that doc exist for each user and the doc has an array called menus.
Here is how i get all the menus for the User stored in the doc
SELECT um from Contacts u UNNEST menus as um where u._type ='user_menu' and u.user_id = "8D6D24A5-D669-45DC-99AC-F257BDA133A4"
to get all menu items avail i use this query
select m.* from Contacts m where _type = 'menu_item'
and what i want to achieve is to get all items from the first query and match them to items on second query where the um = the _id on the second query.
I tried something like this but no luck with JOIN, LEFT or Right
SELECT um, m.* FROM Contacts u LEFT JOIN Contacts AS m ON m._type = 'menu_item' AND u.um = m._id UNNEST u.menus AS um WHERE u._type ='user_menu' AND u.user_id = "8D6D24A5-D669-45DC-99AC-F257BDA133A4"
The above returns all items in the the user_menu table but does not provide any data from the menu_item table in the result