Mongoose include fields from a secondary document using $lookup

190
January 14, 2020, at 1:20 PM

I want to display all of the locations and attach orders (if there are any) to the response.

Locations document example:

{
  id: "LOCATION_A_OBJECTID",
  name: "Location A"
},
{
  id: "LOCATION_B_OBJECTID",
  name: "Location B"
},
{
  id: "LOCATION_C_OBJECTID",
  name: "Location C"
}

Orders document example: (2 orders to A, 1 order to B, 0 orders to C)

{
  id: "5e17a001f1e0220def7a2b5d",
  location: "LOCATION_A_OBJECTID", 
  products: [1,2,3]
},
{
  id: "5e17a001f1e0220def7a2b5d",
  name: "LOCATION_B_OBJECTID",
  products: [1,2,3]
},
{
  id: "5e17a001f1e0220def7a2b5d",
  name: "LOCATION_A_OBJECTID",
  products: [1,2,3]
}

Expected result:

{
  id: "LOCATION_A_OBJECTID",
  name: ...
  products: ...
   ...
},
{
  id: "LOCATION_B_OBJECTID",
  name: ...
  products: ...
  ...
}
...

Edit Will post my schemas here because something is just not right.

Order Schema

const mongoose = require('mongoose')
const orderSchema = mongoose.Schema({
  location: {
    type: mongoose.Schema.Types.ObjectId
  },
  timestamp: {
    type: Date, default: Date.now
  },
  status: Number,
  products: Array,
  total:   Number
})
module.exports = mongoose.model('Order', orderSchema)

Location Schema:

const mongoose = require('mongoose')
const locationSchema = mongoose.Schema({
  name: String,
  address: String
})
module.exports = mongoose.model('Location', locationSchema)
Answer 1

You need $lookup with custom pipeline to match against name or location field and then simply you can run $project to get desired format of your result documents:

db.Locations.aggregate([
    {
        $lookup: {
            from: "Orders",
            let: { loc_id: "$_id" },
            pipeline: [
                { $match: { $expr: { $eq: [ "$$loc_id", "$_id" ] } } },
                { $project: { _id: 0, products: 1 } }
            ],
            as: "orders"
        }
    },
    {
        $match: { orders: { $ne: [] } }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            products: "$orders.products"
        }
    }
])

Mongo Playground

Rent Charter Buses Company
READ ALSO
React native as a frontend and node/express as a backend connected to azure sql database

React native as a frontend and node/express as a backend connected to azure sql database

I am working on react native app using node/express app as a backend api which should be connected to azure sql databaseI have created sql server on azure portal and have created the table

189
Can I give an automatic role to the user with most coins?

Can I give an automatic role to the user with most coins?

I'm trying to figure out how to give an automatic role to the user with most coins on a server based on a json coins system

167
Executing nested WaterfallDialogs - nodejs

Executing nested WaterfallDialogs - nodejs

I'm trying to build a requirement system for order dialogs in our bot, so that we can reuse the main structure for different procedures

145