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]
