SQL2 - Get child node properties

This seems like a rather simple query but I've been banging my head for hours on this. I've got a node structure similar to below:

food-group
    jcr:content
        nuts -> type=almonds
        meat -> beef=true
        fruit -> type=apples,oranges,bananas

There are three types of properties I need to gather from my child nodes: one that is a string, boolean and string array. I would think that the following sql2 query would work and get the properties of them but for whatever reason I'm getting an error:

QUERY

SELECT 
    parent.* 
FROM 
    [cq:PageContent] AS parent 
INNER JOIN 
    [nt:base] as child ON ISCHILDNODE(parent) 
WHERE 
    ISDESCENDANTNODE(parent, [/content/grocerystore/food/])"

ERROR:

Need to specify the selector name because the query contains more than one selector.

Any help is appreciated as I've been at this for the past few days.

Solution

There are two places the ISCHILDNODE function can be used in a JCR-SQL2 query: in the WHERE clause and in the join criteria. Unfortunately, they take different parameters.

Your query is attempting to use the ISCHILDNODE in the join criteria, which requires two parameters: the selector name for the child nodes, and the selector name for the parent nodes.

Here's the query I think you want:

SELECT parent.* 
FROM [cq:PageContent] AS parent 
INNER JOIN [nt:base] as child ON ISCHILDNODE(child,parent) 
WHERE ISDESCENDANTNODE(parent, [/content/grocerystore/food/])

The only change is the parameters to the ISCHILDNODE function.

Unfortunately, the error message doesn't really make this clear. To understand why, it helps to know about the other form of ISCHILDNODE - the one used in the WHERE clause. That form also takes two parameters: the name of the selector that will represent the child nodes, and the literal path of the parent (under which the resulting nodes are children). Here's a contrived query that uses this form:

SELECT node.*
FROM [nt:base] AS node
WHERE ISCHILDNODE(node,[/content/grocerystore])

The query result would contain all nodes that are children of the /content/grocerystore node.

Now, if the query defines just one selector (e.g., a non-join), then there is only one selector name that can be passed to the ISCHILDNODE function. Strictly speaking, the selector is implicitly known, so JCR-SQL2 allows you to pass in just the path. Here's a query that is semantically identical to the previous contrived example:

SELECT node.*
FROM [nt:base] AS node
WHERE ISCHILDNODE([/content/grocerystore])

This is the only form of the ISCHILDNODE that takes a single parameter, and I think this is the form that I think CQ5 is expecting: the error says that the query defines more than one selector, so the selector must be supplied as the first parameter.

Of course, this is very misleading because you're actually using the form of the function that appears in the join criteria. A better error message would say that function requires the child selector name and the parent selector name.