How to get the data based on sub field's values in mongodb using mongoose?

Question

I've a use case where i need to find the role and permissions data from the MongoDB based on parent value.

Here is the sample data.

{
    "_id" : ObjectId("5e8c0dea360c754568906611"),
    "locations" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : false,
        "parent" : "settings"
    },
    "assets" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "assets"
    },
    "tickets" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "tickets"
    },
    "settings" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "settings"
    },
    "user_management" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "user_management"
    },
    "reports" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "reports"
    },
    "dashboard" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "dashboard"
    },
    "ticketPriorities" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : false,
        "parent" : "settings"
    },
    "ticketStatus" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : false,
        "parent" : "settings"
    },
    "users" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : false,
        "parent" : "user_management"
    },
    "permissions" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : false,
        "parent" : "user_management"
    },
    "companySettings" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "companySettings"
    },
    "customFields" : {
        "canCreate" : false,
        "canView" : false,
        "canUpdate" : false,
        "canDelete" : false,
        "isMenu" : false,
        "parent" : "settings"
    },
    "roleName" : "SUPERUSER",
}

Now I want to get the fields (permissions) whose parent is, settings and isMenu is false.

Here is my query I've been using so far.

menuName = "settings";

UserRoles.find({"_id":roleId},{menuName:1});

But am unable to get the data with my query and am little bit confused on how to retrieve that. Please consider my query and suggest me on how to achieve the solution.

newdeveloper · Answer 1 · 2020-04-07T07:18:07.763

It worked for me with this. give it a shot.

db.getCollection('test').find({ "settings.isMenu": true }, { settings: 1})

Output:

/* 1 */

{
    "_id" : ObjectId("5e8c0dea360c754568906611"),
    "settings" : {
        "canCreate" : true,
        "canView" : true,
        "canUpdate" : true,
        "canDelete" : true,
        "isMenu" : true,
        "parent" : "settings"
    }
}

or if your permission collection is just having one record with all the permissions then easiest would be just filtering your needed data using function like _.filter from lodash.

something like this;

const menu = "settings"
const list = _.filter(data[0], {parent: menu, isMenu: false});

Output :

[ { canCreate: true,
    canView: true,
    canUpdate: true,
    canDelete: true,
    isMenu: false,
    parent: 'settings' },
  { canCreate: true,
    canView: true,
    canUpdate: true,
    canDelete: true,
    isMenu: false,
    parent: 'settings' },
  { canCreate: true,
    canView: true,
    canUpdate: true,
    canDelete: true,
    isMenu: false,
    parent: 'settings' },
  { canCreate: false,
    canView: false,
    canUpdate: false,
    canDelete: false,
    isMenu: false,
    parent: 'settings' } ]

Thanks for the help. But here, I'll be getting menuName dynamically. How could I substitute menuName here? ```{dynamicMenu.isMenu}``` — Aravind, Apr 07 '20 at 06:24
if your permission collection is not big then I think easiest solution would be just filer your data using lodash _.filter function like this: const menu = "settings" const list = _.filter(aaa[0], {parent: menu, isMenu: false}); — newdeveloper, Apr 07 '20 at 07:16
Thanks for the answer, @newdeveloper. I've not tried any of the answers as I kept this in hold. I'll try all these. Thanks once again — Aravind, Apr 07 '20 at 09:12

score 2 · Answer 2 · answered Apr 07 '20 at 06:39

you can use $objectToArray

db.getCollection('Test07').aggregate([
    { $project: {  permissions : { $objectToArray: "$$ROOT" } } }, 
    { $unwind:"$permissions"},
    { $match: { "permissions.v.parent" : "settings", 
                "permissions.v.isMenu" : false } }, 
])

score 1 · Answer 3 · answered Apr 07 '20 at 06:44

You need to try it with mongodb-aggregation-pipeline :

db.collection.aggregate([
  /** Retrieve required doc by filtering, will get one doc out */
  {
    $match: {
      _id: ObjectId("5e8c0dea360c754568906611"),
    },
  },
  {
    $project: {
      data: {
        $arrayToObject: {
          $reduce: {
            input: { $objectToArray: "$$ROOT" }, // Convert retrieved doc into an array [{k:...,v:...},{k:...,v:...}] for iteration
            initialValue: [], /** Initial value is [] */
            in: {
              $concatArrays: [
                "$$value", /** Concatinate accumulator with new values */
                {
                  $cond: [
                    {
                      $or: [
                        { $eq: ["$$this.k", "_id"] },
                        { $eq: ["$$this.k", "roleName"] },
                      ],
                    },
                    ["$$this"], /** If k == _id || roleName just add these objects to holding array */
                    {
                      $cond: [
                        {
                          $and: [
                            { $eq: ["$$this.v.isMenu", false] },
                            { $eq: ["$$this.v.parent", "settings"] },
                          ],
                        },
                        ["$$this"], /** If required criteria matches just add these objects to holding array */
                        [], /** If not, add empty array */
                      ],
                    },
                  ],
                },
              ],
            },
          },
        },
      },
    },
  },
  /** Replace newly formed 'data' field as root of document */
  {
    $replaceRoot: {
      newRoot: "$data",
    },
  },
]);

Test : MongoDB-Playground

Note : Don't forget to convert incoming roleId to ObjectId() to match with type of _id in DB - Which initially might a string, Check convert-string-to-objectid-in-mongodb.

pls help if you can for related questions in mongoDB - https://stackoverflow.com/questions/61067856/calculate-dwell-time-between-2-statuses-of-a-field — newdeveloper, Apr 07 '20 at 07:19

How to get the data based on sub field's values in mongodb using mongoose?

3 Answers3