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

Viewed 503

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"}, ...]
+----------------------------------------------------------------+
1 Answers
Related