Querying a MongoDB with nested schema in Apache Drill

1
votes

I have a simple Mongo database (further simplified as below), that I am querying with Apache Drill.

  1. Create a json file called small.json with the below line:

{"_id":{"$oid":"56a784b76952647b7b51c562"},"provenance":{"image": {"case_id":"TCGA-TS2","subject_id":"TCGA"}}}

  1. Create a Mongo DB with the small.json as below:

mongoimport --db users --collection contacts --type small.json

  1. I can query the above data base just fine as below, and get the results.

SELECT camic.provenance.image.case_id caseid

FROM mongo.users.contacts camic

+-----------+

| caseid |

+-----------+

| TCGA-TS2 |

+-----------+

1 row selected (1,26 seconds)

However, the query fails, when a "WHERE" is involved with a nested element, as below.

SELECT camic.provenance.image.case_id caseid FROM mongo.users.contacts camic WHERE caseid = 'TCGA-TS2';

(The expected outcome is the same as above without the WHERE clause).

This probably is a bug, and I have reported it: https://issues.apache.org/jira/browse/DRILL-4855

Is there any workaround to get the outcome involving nested elements in Mongo using Apache Drill?

1
jsonmongodbapache-drill

1 Answers

1
votes

SELECT camic.provenance.image.case_id caseid FROM mongo.users.contacts camic WHERE camic.provenance.image.case_id = 'TCGA-TS2';

We cannot reference column aliases in the following clauses:

  • WHERE
  • GROUP BY
  • HAVING

http://drill.apache.org/docs/select-statements/