Querying a MongoDB with nested schema in Apache Drill

Question

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"}}}

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

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

3. 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?

Answer 1

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/