Problems with array expansion in a query

[Robert Patt-Corner]

Having a problem, hopefully easily resolved, expanding an array in a query.  We have a nesting that ends in a fairly straightforward structure that ends in a priorities array, like this:

A query against a direct array element works:

SELECT  

planYear[0].form.priorities.subItems.programPriorities.priorities[0].describeAnyProblems

FROM `somepath`

WHERE (where clause)

and any number in the brackets is fine.

But what we want is the describeAnyProblems field across all array elements.  Yet the expected query returns no results:

SELECT  

planYear[0].form.priorities.subItems.programPriorities.priorities[*].describeAnyProblems

FROM `somepath`

WHERE (where clause)

Any thoughts?

[Robert Patt-Corner]

(Version is 4.2)

[do...@slamdata.com]

Hi Robert, while it shouldn't matter, but can you provide the full WHERE clause so we can verify that something there isn't stopping data from coming through when using the [*] operator on your array?

[Robert Patt-Corner]

Here's one ... works with a number, fails on asterisk:

SELECT  state as state,

planYear[0].form.priorities.subItems.programPriorities.priorities[*].describeNeed as priorities

FROM `/somepath`

WHERE state = "CA" and FY = `2016` and programActivityCode = "somecode" and planYear[0].version.isLatest = true

ORDER BY state desc

[Robert Patt-Corner]

So ... the problem is indeed with the WHERE clause ... but it should work.

Remove the 

and planYear[0].version.isLatest = true

and the query returns results.

However that clause really ought to work, shouldn't it.  In a Mongo query I can, for example:

db.myCollection.find({"planYear.0.version.isLatest":true })

and get a full range of results.

[Robert Patt-Corner]

Interestingly, SQL2 itself seems to think the field is available ... sneaking the problematic field into the SELECT clause (but not the WHERE clause) yields a full returned set of documents (all of which show a TRUE value)

SELECT  state as state, planYear[0].version.isLatest, ...

Could I be making an error in the boolean spec: