'MongoDB query nested array
I have some documents in MongoDB with tables inside like the following:
doc 1
__________________________ __________________________
| Table 1a | | Table 1b |
| apples | bananas | pears | | apples | bananas | pears |
|--------|---------|-------| |--------|---------|-------|
| 1 | 2 | 3 | | 1 | 5 | 3 |
| 4 | 5 | 6 | | 2 | 4 | 6 |
| 7 | 8 | 9 | | 1 | 9 | 6 |
‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾ ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
doc 2
__________________________ __________________________
| Table 2a | | Table 2b |
| apples | bananas | pears | | apples | bananas | pears |
|--------|---------|-------| |--------|---------|-------|
| 5 | 2 | 7 | | 1 | 6 | 5 |
| 4 | 4 | 1 | | 7 | 2 | 4 |
| 3 | 9 | 8 | | 8 | 9 | 3 |
‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾ ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
which I represented in MongoDB with the following structure:
[
{
"name": "doc 1",
"tables": [
{
"name": "table a",
"lines": [
{
"key_values": [
{
"key": "apples",
"value": "1"
},
{
"key": "bananas",
"value": "2"
},
{
"key": "pears",
"value": "3"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "4"
},
{
"key": "bananas",
"value": "5"
},
{
"key": "pears",
"value": "6"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "7"
},
{
"key": "bananas",
"value": "8"
},
{
"key": "pears",
"value": "9"
}
]
}
]
},
{
"name": "table b",
"lines": [
{
"key_values": [
{
"key": "apples",
"value": "1"
},
{
"key": "bananas",
"value": "5"
},
{
"key": "pears",
"value": "3"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "2"
},
{
"key": "bananas",
"value": "4"
},
{
"key": "pears",
"value": "6"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "1"
},
{
"key": "bananas",
"value": "9"
},
{
"key": "pears",
"value": "6"
}
]
}
]
}
]
},
{
"name": "doc 2",
"tables": [
{
"name": "table a",
"lines": [
{
"key_values": [
{
"key": "apples",
"value": "5"
},
{
"key": "bananas",
"value": "2"
},
{
"key": "pears",
"value": "7"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "4"
},
{
"key": "bananas",
"value": "4"
},
{
"key": "pears",
"value": "1"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "3"
},
{
"key": "bananas",
"value": "9"
},
{
"key": "pears",
"value": "8"
}
]
}
]
},
{
"name": "table b",
"lines": [
{
"key_values": [
{
"key": "apples",
"value": "1"
},
{
"key": "bananas",
"value": "6"
},
{
"key": "pears",
"value": "5"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "7"
},
{
"key": "bananas",
"value": "2"
},
{
"key": "pears",
"value": "4"
}
]
},
{
"key_values": [
{
"key": "apples",
"value": "8"
},
{
"key": "bananas",
"value": "9"
},
{
"key": "pears",
"value": "3"
}
]
}
]
}
]
}
]
I would like to be able to query documents by table content, for example, finding the document with table that has apples==1 && pears==3 appearing on the same line, which in this case should match doc 1 as table 1a and table 1b are a match (while table 2b is not a match as apples==1 and pears==3 do not appear on the same line)
I understand how to query for a key-value,
db.collection.aggregate([
{
"$match": {
"tables.lines": {
"$elemMatch": {
"key_values": {
"$elemMatch": {
"key": "key_a_1",
"value": "value_a_1_1"
}
}
}
}
}
}
])
however I am not sure how to take this one step further to look on the same line. Also, I would like to match using regular expressions, i.e. search for bana rather than specifying the full text banana.
Is this even possible?
I also created an example in the Mongo Playground
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|