N1QL query always returns an empty set

SQL++
1

Hi, I’m trying to perform this query with N1QL, but he replies me with an empty result set, and I can’t figure out why.
The query is the following:

SELECT sons.*, (SELECT grandsons.* FROM default grandsons WHERE grandsons.parent_id = sons.objectId) AS nodes FROM default main 
JOIN default sons ON KEYS main.nodes 
WHERE main.type = 'root' 
AND main.objectId = 'root::d6a89d80-e2da-11e4-908b-f18a9604f194' 
group by sons

The purpose is to retrieve the first and second level nodes of a tree structured as follows:
Every node has an array field ‘nodes’, which contains the ids of his direct sons. Every node except the root has also a ‘parent_id’ field, that represents the id of the parent of this node in the tree structure.

NB: The query without the subquery returns a not-empty result set.

Have I misunderstood something on N1QL’s subqueries behavior?

[EDIT] Sorry for the inconvenience, I’ve forgotten that in subqueries with a from clause is mandatory to insert an USE KEYS clause! I restructured the query and now it works!

2

No worries. I hope you were getting a syntax error indicating that subqueries require USE KEYS.

3

I noticed that when I execute this query the ‘nodes’ array on the items retrieved with the ‘sons’ identifier, is like the following:
[ {“default”: {…}}, {“default”: {…}}]

Is there any nifty workaround to turn it into the following output?
[{…},{…}]

Is there another way to do this query without using subqueries? I tried it using two nested joins statements but I wasn’t able to retrieve the data correctly.

4

In your subquery, use

SELECT RAW grandsons.*

instead of

SELECT grandsons.*

5

Is this ‘RAW’ statement introduced on N1QL v4? Because I’m running N1QL v3 and I get syntax error

6

My bad. Try

SELECT RAW grandsons

7

I noticed that ‘SELECT RAW’ doesn’t make the ORDER BY statement work. When I perform a RAW query with some ordination logic, my result set is always unordered