r - Mongolite 和在 ObjectId 与字符上使用 $lookup 进行聚合

Question

使用 mongolite v0.9.1 (R) 和 MongoDB v3.4，我想加入两个集合，第一个，包含 ObjectId 的父集合，第二个，包含父字符串值的子集合' 对象 ID。

这是基本语法:

conParent$aggregate('[
    { "$lookup":
        { "from":"Children", 
          "localField": "_id",
          "foreignField": "parent_id",
          "as": "children"
        }
    }
 ]')

$lookup 似乎只接受字段名，我试过这个，产生语法错误:

           .../...
           "foreignField": "{'$oid':'parent_id'}"
           .../...

那么有什么办法可以解决吗？

另一方面，我尝试以 ObjectId 格式将父对象的 ObjectId 保存在子对象中，但没有成功(我仍然在 MongoDB 中得到一个字符串):

   result <- transform(
                computeFunction, 
                parent_id = sprintf('{"$oid":"%s"}',parent$"_id"))
   resultCon <- conout$insert(as.data.frame(result))

是否可以在 mongolite 中将 Id 存储为 ObjectId？

注意:我正在进行批量插入，因此无法处理 JSON 字符串操作。

有什么想法吗？

编辑:

这是我正在使用的集合的示例:

父集合:

{
    "_id" : ObjectId("586f7e8b837abeabb778d2fd"),
    "name" : "Root1",
    "date" : "2017-01-01",
    "value" : 1.0,
    "value1" : 10.0,
    "value2" : 100.0
},
{
    "_id" : ObjectId("586f7ea4837abeabb778d30a"),
    "name" : "Root1",
    "date" : "2017-01-02",
    "value" : 2.0,
    "value1" : 20.0,
    "value2" : 200.0
}

child 系列:

{
    "_id" : ObjectId("586f7edf837abeabb778d319"),
    "name" : "Item1",
    "value" : 1.1,
    "date" : "2017-01-01",
    "parent_id" : "586f7e8b837abeabb778d2fd"
}
{
    "_id" : ObjectId("586f7efa837abeabb778d324"),
    "name" : "Item2",
    "value1" : 11.111111111,
    "value2" : 12.222222222,
    "date" : "2017-01-01",
    "parent_id" : "586f7e8b837abeabb778d2fd"
}
{
    "_id" : ObjectId("586f7f15837abeabb778d328"),
    "name" : "Item1",
    "value" : 2.2,
    "date" : "2017-01-02",
    "parent_id" : "586f7ea4837abeabb778d30a"
}
{
    "_id" : ObjectId("586f7f2b837abeabb778d32e"),
    "name" : "Item2",
    "value1" : 21.111111111,
    "value2" : 22.222222222,
    "date" : "2017-01-02",
    "parent_id" : "586f7ea4837abeabb778d30a"
}

Answer 1

你能试试吗:

"foreignField": "_id" 

从 mongo 的网站示例开始:

library(mongolite)
library(jsonlite)

a = '[{ "_id" : 1, "item" : 1, "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : 2, "price" : 20, "quantity" : 1 },
{ "_id" : 3  }]'

b= '[{ "_id" : 1, "sku" : "abc", "description": "product 1", "instock" : 120 },
{ "_id" : 2, "sku" : "def", "description": "product 2", "instock" : 80 },
{ "_id" : 3, "sku" : "ijk", "description": "product 3", "instock" : 60 },
{ "_id" : 4, "sku" : "jkl", "description": "product 4", "instock" : 70 },
{ "_id" : 5, "sku": null, "description": "Incomplete" },
{ "_id" : 6 }]'

mongo_orders <- mongo(db = "mydb", collection = "orders")
mongo_orders$insert(fromJSON(a))

mongo_inventory <- mongo(db = "mydb", collection = "inventory")
mongo_inventory$insert(fromJSON(b))

df <- mongo_orders$aggregate('[
    {
      "$lookup":
        {
          "from": "inventory",
          "localField": "item",
          "foreignField": "_id",
          "as": "inventory_docs"
        }
   }
]')

str(df)

当两者都设置为 _id 时，它也能正常工作

"localField": "_id",
      "foreignField": "_id",