That was my understand of UNNEST as well. In any case, unfortunately the query you gave doesn't work either. I logged a JIRA case
https://issues.apache.org/jira/browse/CALCITE-1705 If anyone has any hints on how I might go about fixing this, that would be appreciated :) -- Michael Mior [email protected] 2017-03-16 20:20 GMT-04:00 Julian Hyde <[email protected]>: > UNNEST converts a non-relational value to a relation. (I use the word > “non-relational” meaning an expression that should occur in the SELECT or > WHERE clause rather than the FROM clause. But due to the nature of UNNEST > the value will be a collection type.) > > You have given it a query, so it interprets that query (I think) as a > scalar sub-query. > > Here’s how I’d write your query: > > select * > from "mongo_raw".”publishers” as p, > unnest(cast(p._MAP['books'] as varchar multiset)); > > Now, whether it works is another matter… please log bugs... > > Julian > > > > On Mar 16, 2017, at 10:33 AM, Michael Mior <[email protected]> wrote: > > > > I have a MongoDB collection named publishers where each document looks > like > > the following: > > > > {"_id":"987654321","name":"O’Reilly > > Media","founded":1980,"books":["123456789","000000000"]} > > > > I want to get results which look like this > > > > _id | books > > --------------------------------- > > 987654321 | 123456789 > > 987654321 | 000000000 > > > > So far, I've tried a query like the following, but I get a NPE (stack > trace > > later) > > > > select * from unnest(select cast(_MAP['books'] as varchar multiset) from > > "mongo_raw"."publishers"); > > > > I'm not sure if this is an issue with Calcite or my misunderstanding of > the > > semantics of the UNNEST operator. Any help appreciated :) > > > > Stack trace for the NPE follows: > > > > Error: Error while executing SQL "select * from unnest(select > > cast(_MAP['books'] as varchar multiset) from "publishers")": null > > (state=,code=0) > > java.sql.SQLException: Error while executing SQL "select * from > > unnest(select cast(_MAP['books'] as varchar multiset) from > "publishers")": > > null > > at org.apache.calcite.avatica.Helper.createException(Helper. > java:56) > > at org.apache.calcite.avatica.Helper.createException(Helper. > java:41) > > at > > org.apache.calcite.avatica.AvaticaStatement.executeInternal( > AvaticaStatement.java:156) > > at > > org.apache.calcite.avatica.AvaticaStatement.execute( > AvaticaStatement.java:209) > > at sqlline.Commands.execute(Commands.java:822) > > at sqlline.Commands.sql(Commands.java:732) > > at sqlline.SqlLine.dispatch(SqlLine.java:813) > > at sqlline.SqlLine.begin(SqlLine.java:686) > > at sqlline.SqlLine.start(SqlLine.java:398) > > at sqlline.SqlLine.main(SqlLine.java:291) > > Caused by: java.lang.NullPointerException > > at > > org.apache.calcite.sql2rel.StandardConvertletTable.convertCast( > StandardConvertletTable.java:525) > > at > > org.apache.calcite.sql2rel.StandardConvertletTable$1.convertCall( > StandardConvertletTable.java:116) > > at > > org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall( > SqlNodeToRexConverterImpl.java:61) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard. > visit(SqlToRelConverter.java:4397) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard. > visit(SqlToRelConverter.java:3765) > > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard. > convertExpression(SqlToRelConverter.java:4299) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList( > SqlToRelConverter.java:3605) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl( > SqlToRelConverter.java:659) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect( > SqlToRelConverter.java:616) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive( > SqlToRelConverter.java:2933) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryOrInList( > SqlToRelConverter.java:1514) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertExists( > SqlToRelConverter.java:1491) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.substituteSubQuery( > SqlToRelConverter.java:1173) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.replaceSubQueries( > SqlToRelConverter.java:990) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom( > SqlToRelConverter.java:2051) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom( > SqlToRelConverter.java:1913) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl( > SqlToRelConverter.java:635) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect( > SqlToRelConverter.java:616) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive( > SqlToRelConverter.java:2933) > > at > > org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery( > SqlToRelConverter.java:552) > > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare. > java:236) > > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare. > java:200) > > at > > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_( > CalcitePrepareImpl.java:761) > > at > > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_( > CalcitePrepareImpl.java:617) > > at > > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql( > CalcitePrepareImpl.java:587) > > at > > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery( > CalciteConnectionImpl.java:214) > > at > > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute( > CalciteMetaImpl.java:595) > > at > > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal( > AvaticaConnection.java:615) > > at > > org.apache.calcite.avatica.AvaticaStatement.executeInternal( > AvaticaStatement.java:148) > > ... 7 more > > > > -- > > Michael Mior > > [email protected] > >
