CODEWITHSUNDEEP
jsonmongodb
JokerMartini
JokerMartini

Reputation: 6147

MongoDB: Select from one collection based on another collection

I'm new to MongoDB and I'm trying to find a category that meets the required params. I have two collections, categories (list of categories) and ref_categories (manages nested category relationships)

collection categories:
[
  {
      "id": "A1001",
      "key": "3dmodels",
  },
  {
      "id": "A1002",
      "key": "animals",
  },
  {
      "id": "A1003",
      "key": "birds",
  },
  {
      "id": "A1004",
      "key": "reptiles",
  },
  {
      "id": "A1005",
      "key": "birds",
  }
]

collection categories_ref:
[
  {
      "category_id": "A1001", // 3dmodels parented to
      "p_category_id": "root", // root
  },
  {
      "category_id": "A1002", // animals parented to
      "p_category_id": "A1001", // 3dmodels
  },
  {
      "category_id": "A1003", // birds parented to
      "p_category_id": "A1002", // animals
  },
  {
      "category_id": "A1004", // reptiles parented to
      "p_category_id": "A1002", // animals
  },
  {
      "category_id": "A1005", // birds parented to
      "p_category_id": "A1004", // reptiles
  }
]

You'll noticed in my Categories collection there are two entries for 'birds' however they each have a different parent category.

I'm trying to create a query that allows me to find the category by key and parent category key.

Pseudo example.... I want to find 'birds' but the entry who has a parent called 'animals'. Otherwise return null.

the expected output would be

// find category_key: "birds" parent_category_key: "animals"

{
   "id": "A1003",
   "key": "birds",
}

Upvotes: 0

Views: 786

Answers (1)

J.F.
J.F.

Reputation: 15235

You can use a $lookup with a pipeline where you match two conditions:

  1. Join based on id. Categories id is the same as reference category_id.
  2. Also check p_category_id is the desired code.

And after that you can $match to not get elements where the join result is empty (i.e, keep elements where exists a value).

db.categories.aggregate([
  {
    "$match": {"key": "birds"}
  },
  {
    "$lookup": {
      "from": "categories_ref",
      "let": {"id": "$id"},
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$eq": ["$category_id","$$id"]
                },
                {
                  "$eq": ["$p_category_id","A1002"]
                }
              ]
            }
          }
        }
      ],
      "as": "cat_ref"
    }
  },
  {
    "$match": {"cat_ref": {"$ne": []}}
  },
  {
    "$project": {"cat_ref": 0}
  }
])

Example here

Upvotes: 1

Related Questions