I have two city and state Collections as follows:
cities
{
"_id" : ObjectId("5be385fdb9ee9a25f7e418a9"),
"ID" : "1",
"Nome" : "Afonso Cláudio",
"Estado" : "8"
},{
"_id" : ObjectId("5be385fdb9ee9a25f7e418aa"),
"ID" : "2",
"Nome" : "Água Doce do Norte",
"Estado" : "8"
}
states
{
"_id" : ObjectId("5be385feb9ee9a25f7e42e65"),
"ID" : "1",
"Sigla" : "AC",
"Nome" : "Acre"
}
I want to create a new collection from these two. For this I wrote the following query
db.cidades.aggregate([
{ $lookup: { from:"estados",localField: "Estado",foreignField: "ID",as:"estado"}},
{ $sort : { Estado:1,Nome:1 }},
{ $project:{ "ID":0,"_id":0,"Estado":0,"estado.ID":0,"estado._id":0}},
{ $addFields: { "cnpj": null,"legislacao": [] }},
{ $out : "municipios" }
]).pretty()
What gave me the following result:
{
"_id" : ObjectId("5be5c6407608e1137ab33fe4"),
"Nome" : "Acrelândia",
"estado" : [
{
"Sigla" : "AC",
"Nome" : "Acre"
}
],
"cnpj" : null,
"legislacao" : []
}
What I would really like is the end result looks like this:
{
"_id" : ObjectId("5be5c6407608e1137ab33fe4"),
"Nome" : "Acrelândia",
"Sigla" : "AC",
"Nome" : "Acre"
"cnpj" : null,
"legislacao" : []
}
or so:
{
"_id" : ObjectId("5be5c6407608e1137ab33fe4"),
"Nome" : "Acrelândia",
"estado" :{
"Sigla" : "AC",
"Nome" : "Acre"
}
,
"cnpj" : null,
"legislacao" : []
}
What modifications should I make in the query to get the above result?