英文:
mongodb aggergate with nested arrays of objects inside an array of objects
问题
以下是翻译好的代码部分：
我得到了以下的数据结构：
data = {
"_id": "abc",
"field": "value",
"misc": {
"height": 1.1,
},
"sales": [
{
"label": 1,
"codes": [
{
"code": 123456,
"active": True
},
{
"code": 234567,
"active": False
},
{
"code": 345678,
"active": False
},
],
"prices": [
{
"price": 2.99,
"valid_from": datetime(year=2023, month=3, day=1)
},
{
"price": 3.99,
"valid_from": datetime(year=2023, month=4, day=1)
},
{
"price": 4.99,
"valid_from": datetime(year=2023, month=5, day=1)
},
{
"price": 5.99,
"valid_from": datetime(year=2023, month=6, day=1)
},
]
},
{
"label": 2,
"codes": [
{
"code": 987654,
"active": True
},
{
"code": 876543,
"active": False
},
{
"code": 765432,
"active": False
},
],
"prices": [
{
"price": 2.99,
"valid_from": datetime(year=2023, month=3, day=1)
},
{
"price": 3.99,
"valid_from": datetime(year=2023, month=4, day=1)
},
{
"price": 4.99,
"valid_from": datetime(year=2023, month=5, day=1)
},
{
"price": 6.99,
"valid_from": datetime(year=2023, month=6, day=1)
},
}
},
]
}
我的目标是输出标签为1的对象。对我来说，这个筛选聚合操作可以工作：
db.MasterData.aggregate([
{ "$match": { "_id": "abc" } },
{
"$project": {
"field": 1,
"sales": {
"$filter": {
"input": "$sales",
"as": "item",
"cond": {
"$eq": ["$$item.label", 1]
}
}
},
}
}
])
但我还想筛选嵌套数组codes和prices，例如只显示active为True的code和大于今天的价格。但我无法弄清楚如何访问嵌套数组。这个查询导致空结果：
db.MasterData.aggregate([
{ "$match": { "_id": "abc" } },
{
"$project": {
"field": 1,
"sales.codes": {
"$filter": {
"input": "$sales.codes",
"as": "item",
"cond": {
"$eq": ["$$item.active", True]
}
}
},
}
}
])
有人能帮我吗？
此外，我在思考这是否是处理这种数据结构的最佳方式。是否将销售部分插入到另一个集合并引用主数据会更好？据我所知，根据我目前对MongoDB的了解，应尽量避免使用引用文档，除非必要。
英文:
I got following data structure:
data = {
"_id": "abc",
"field": "value",
"misc": {
"height": 1.1,
},
"sales": [
{
"label": 1,
"codes": [
{
"code": 123456,
"active": True
},
{
"code": 234567,
"active": False
},
{
"code": 345678,
"active": False
},
],
"prices": [
{
"price": 2.99,
"valid_from": datetime(year=2023, month=3, day=1)
},
{
"price": 3.99,
"valid_from": datetime(year=2023, month=4, day=1)
},
{
"price": 4.99,
"valid_from": datetime(year=2023, month=5, day=1)
},
{
"price": 5.99,
"valid_from": datetime(year=2023, month=6, day=1)
},
]
},
{
"label": 2,
"codes": [
{
"code": 987654,
"active": True
},
{
"code": 876543,
"active": False
},
{
"code": 765432,
"active": False
},
],
"prices": [
{
"price": 2.99,
"valid_from": datetime(year=2023, month=3, day=1)
},
{
"price": 3.99,
"valid_from": datetime(year=2023, month=4, day=1)
},
{
"price": 4.99,
"valid_from": datetime(year=2023, month=5, day=1)
},
{
"price": 6.99,
"valid_from": datetime(year=2023, month=6, day=1)
},
]
},
]
}
My Goal is to output the label 1 object. This Filter Aggregate works for me:
db.MasterData.aggregate([
{ "$match": { "_id": "abc" } },
{
"$project": {
"field": 1,
"sales": {
"$filter": {
"input": "$sales",
"as": "item",
"cond": {
"$eq": ["$$item.label", 1]
}
}
},
}
}
])
But I want also to filter the nested arrays codes and prices, for example show only the code where active is True and only the prices which are greater than today. But I couldn’t figure out how to access the nested arrays. This query results in an empty outcome:
db.MasterData.aggregate([
{ "$match": { "_id": "abc" } },
{
"$project": {
"field": 1,
"sales.codes": {
"$filter": {
"input": "$sales.codes",
"as": "item",
"cond": {
"$eq": ["$$item.active", True]
}
}
},
}
}
])
Can someone help me with this please?
Also, I am wondering if this is the best way to deal with this Data structure. Would it be better to insert the sales Part in another collection with reference to the Main Data? As far as I know at this point of my MongoDB journey, using reference documents should be avoid when it could be.
答案1
得分: 1
以下是翻译好的内容：
也许是这样的：
db.collection.aggregate([
{
"$match": {
"_id": "abc"
}
},
{
"$addFields": {
"sales": {
"$filter": {
"input": "$sales",
"as": "s",
"cond": {
"$eq": ["$$s.label", 1]
}
}
}
}
},
{
"$addFields": {
"sales": {
"$map": {
"input": "$sales",
"as": "s",
"in": {
"$mergeObjects": [
"$$s",
{
"codes": {
"$filter": {
"input": "$$s.codes",
"as": "c",
"cond": {
"$eq": ["$$c.active", true]
}
}
},
"prices": {
"$filter": {
"input": "$$s.prices",
"as": "p",
"cond": {
"$gt": ["$$p.valid_from", 1]
}
}
}
}
]
}
}
}
}
}
])
解释：
- 匹配具有"_id"等于"abc"的文档。
- 使用"$addFields"来筛选仅具有"label"等于1的销售对象。
- 使用"$addFields"将销售对象内的"codes"和"prices"字段分别筛选为仅包含活动的代码和有效价格。
您还可以将2)和3)组合在单个嵌套的"$addFields"中，如此示例链接中所示。
英文:
Maybe something like this:
db.collection.aggregate([
{
"$match": {
"_id": "abc"
}
},
{
"$addFields": {
"sales": {
"$filter": {
"input": "$sales",
"as": "s",
"cond": {
"$eq": [
"$$s.label",
1
]
}
}
}
}
},
{
"$addFields": {
"sales": {
"$map": {
"input": "$sales",
"as": "s",
"in": {
"$mergeObjects": [
"$$s",
{
codes: {
"$filter": {
"input": "$$s.codes",
"as": "c",
"cond": {
"$eq": [
"$$c.active",
true
]
}
}
},
prices: {
"$filter": {
"input": "$$s.prices",
"as": "p",
"cond": {
"$gt": [
"$$p.valid_from",
1
]
}
}
}
}
]
}
}
}
}
}
])
Explained:
- Match the document with _id="abc"
- addFields1 to filter only objects from sales[] having label=1
- addFields2/map/mergeObjects to filter only the active codes[] and valid prices[] inside the sales[] array.
You can also combine the 2) and 3) in single nested addFields as follow here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论