MongoDB Aggregate Array with Two Fields

Question

I have vehicles collection with the following schema, all the articles are just general products (no child products included):

{
    "_id" : ObjectId("554995ac3d77c8320f2f1d2e"),
    "model" : "ILX",
    "year" : 2015,
    "make" : "Acura",
    "motor" : {
        "cylinder" : 4,
        "liters" : "1.5"
    },
    "products" : [
        ObjectId("554f92433d77c803836fefe3"),
        ...
    ]
}

And I have products collection, some of them are general products related with warehouse sku's and some products are "son" products that fit in multiples general products, these son products are also related with warehouse sku's:

general products

{
  "_id" : ObjectId("554b9f223d77c810e8915539"),
  "brand" : "Airtex",
  "product" : "E7113M",
"type" : "Fuel Pump",
"warehouse_sku" : [
    "1_5551536f3d77c870fc388a04",
    "2_55515e163d77c870fc38b00a"
]
}

child product

{
    "_id" : ObjectId("55524d0c3d77c8ba9cb2d9fd"),
    "brand" : "Performance",
    "product" : "P41K",
    "type" : "Repuesto Bomba Gasolina",
    "general_products" : [
        ObjectId("554b9f223d77c810e8915539"),
        ObjectId("554b9f123d77c810e891552f")
    ],
    "warehouse_sku" : [
        "1_555411043d77c8066b3b6720",
        "2_555411073d77c8066b3b6728"
    ]
}

My question is to obtain a list of general products (_id and general_products inside child products) for warehouse_sku that follow the pattern : 1_

I have created an aggregate query with the following structure:

list_products = db.getCollection('products').aggregate([
...  {$match: {warehouse_sku: /^1\_/}},
...  {$group: { "_id": "$_id" } }
... ])

And that query give me successfully a result :

{ "_id" : ObjectId("55524d0c3d77c8ba9cb2d9fd") }
{ "_id" : ObjectId("554b9f223d77c810e8915539") }

but I need to obtain a list of general products so I can use $in in the vehicles collection.

list_products = [ ObjectId("55524d0c3d77c8ba9cb2d9fd"), ObjectId("554b9f223d77c810e8915539")]

example: db.vehicles.find({products:{$in: list_products}})

This last query I could not achieve it.

chridam · Accepted Answer · 2015-05-14 15:09:40Z

2

Use the aggregation cursor's map() method to return an array of ObjectIds as follows:

var pipeline = [
    {$match: {warehouse_sku: /^1\_/}},
    {$group: { "_id": "$_id" } }
],
list_products = db.getCollection('products')
                  .aggregate(pipeline)
                  .map(function(doc){ return doc._id });

The find() cursor's map() would work here as well:

var query = {'warehouse_sku': /^1\_/},
    list_products = db.getCollection('products')
                      .find(query)
                      .map(function(doc){ return doc._id });

UPDATE

In pymongo, you could use a lambda function with the map function. Because map expects a function to be passed in, it also happens to be one of the places where lambda routinely appears:

import re
regx = re.compile("^1\_", re.IGNORECASE)
products_cursor = db.products.find({"warehouse_sku": regx})
list_products = list(map((lambda doc: doc["_id"]), products_cursor))

edited May 14, 2015 at 15:09

answered May 14, 2015 at 14:33

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

technology_dreamer Over a year ago

Fantastic it gives me the result, could it be possible to obtain the same result without using map?...I'm using PyMongo

chridam Over a year ago

@technology_dreamer Yes, it's possible, check my updated answer. Though I haven't yet tested it but it should work.

Collectives™ on Stack Overflow

MongoDB Aggregate Array with Two Fields

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related