how does the JOIN queries work with collection can create a JOIN query with two collections? I saw a example of JOIN in docs which is very old 2018 where they dont have collections
Collections can be joined in Query. You can think of collections as tables in relational database.
Here’s an example:
SELECT f.name, c.color FROM test.flowers AS f JOIN test.colors AS c ON f.cid = c.cid ORDER BY f.name
The database has a scope, “test” containing at least two collections, “flowers” and “colors”. Documents in the flowers collection have a field called “name” and a field called “cid” which is a foreign key into the collection “colors” (“cid”). Documents in the colors collection have an additional field “color”.