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