How can i create a join between an array in a doc and other docs?

SQL++
1

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

2

Hi @aponnath ,

Your query is close, Just need to move UNNEST before JOIN because UNNEST value used in ON clause

Example 13 ANSI JOIN Support in N1QL: Overview + Details | Couchbase

SELECT um, m.*
FROM Contacts u
UNNEST u.menus AS um
LEFT JOIN Contacts AS m ON m._type = "menu_item" AND um = m._id
WHERE u._type ="user_menu" AND u.user_id = "8D6D24A5-D669-45DC-99AC-F257BDA133A4";
3

Thanks, i didn’t know that it matters where the unnest is, but i guess it makes sense that the query commands get executed based on where they are. Would be nice if the query tool would catch stuff like this if you try to reference a unnested value before it is avail like in this case.

4

normally it gives error. So happens ON clause has qualified with u.um so assumed it is part of u

as of know JOIN order is LEFT to RIGHT . In next release with CBO it can change based on the cost.