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]
