Apache Drill with mongodb. Query for documents with particular keys and values within a map

Question

I use apache drill with mongodb. My documents in mongodb look like this:

{
  "labels": [{
              "key": "product",
              "value": "shampoo"
              },
              {
               "key": "id",
               "value": "1"
               },
              {
               "key": "number",
               "value": "0"
               }]
 },
 "labels": [{
              "key": "product",
              "value": "shampoo"
              },
              {
               "key": "id",
               "value": "2"
               },
              {
               "key": "number",
               "value": "1"
               }]
 }

And I want to query for documents which have e. g. a label {"key": "id", "value": "1"}

I tried it with this query:

select * from myCollection where `labels.key` = 'id' and `labels.value` = '1';

It should return only the first document, but it returns both, because the second document has a label with key = number and value = 1 (it is so because of the value).

Is there a possibility to obtain only the first document? How the query should look like?

Drill converts the collection to such table:

+---------------------------------------------------------------+
                                labels
+---------------------------------------------------------------+
[{"key": "product", "value": "shampoo"}, {"key": "id", "value": "1"},...]
[{"key": "product", "value": "shampoo"}, {"key": "id", "value": "2"}, ...]
+----------------------------------------------------------------+

Answer 1

This is not possible in Apache Drill.

In MongoDB you would express this criterion using the $elemMatch operator, for example:

db.collection.find(
   { labels: { $elemMatch: { key: "id", value: "1" } } }
)

However, Apache Drill's Mongo storage plugin does not support the $elemMatch operator.

The original documentation for Apache Drill's Mongo storage plugin stated:

As of now, predicate pushdown is implemented for the following filters: >, >=, <, <=, ==, !=, isNull and isNotNull.

Looking at the latest version of the code this remains the case so ... no support for $elemMatch and there is no relational operator which can perform this match for you.