Query in Embedded Document

Asked

Viewed 36 times

1

I’m trying to make a query to return only bank 1 investments that are not savings. How do I do? I have tried several ways and it has not worked so far.

I followed the following query:

db.Bancos.find( { "investimentos": { $elemMatch: { bank: "banco1", productName: {$ne:"Poupanca"} } } } );

{
  "_id": "5d3fc8c3914297c7b9a3a9e5",
  "banco": "banco 1",
  "investimentos": [{
      "bank": "banco1",
      "risk": "Conservador",
      "expiryDate": "2021-10-04",
      "tax": "1.02",
      "discriminator": "investment",
      "productName": "LCI"
    },

    {
      "bank": "banco1",
      "risk": "Conservador",
      "expiryDate": "2020-06-24",
      "tax": "0.75",
      "discriminator": "investment",
      "productName": "Fundo DI"
    },

    {
      "bank": "banco1",
      "risk": "Conservador",
      "tax": "0.04",
      "discriminator": "investment",
      "id": "259ad8ac-57b7-4d33-8e75-46cf5c5c28e3",
      "aniversary": "30",
      "productName": "Poupanca"
    }
  }]


  {
    "_id": "5d3fcb4c914297c7b9a3a9e6",
    "banco": "banco2",
    "investimentos": [{
        "bank": "banco2",
        "risk": "Conservador",
        "expiryDate": "2020-06-24",
        "tax": "0.80",
        "discriminator": "investment",
        "id": "73db503f-c780-448c-a6a8-05d2837ff6ff",
        "redemptionDate": "D+1",
        "productName": "Fundo DI"
      }

      ,
      {
        "bank": "banco2",
        "risk": "Conservador",
        "expiryDate": "2020-12-17",
        "tax": "0.98",
        "discriminator": "investment",
        "id": "54e01515-dc7f-470f-8f00-8603c8f00686",
        "productName": "LCA"
      },

      {
        "bank": "banco2",
        "risk": "Conservador",
        "expiryDate": "2021-08-05",
        "tax": "1.0",
        "discriminator": "investment",
        "id": "259ad8ac-57b7-4d33-8e75-46cf5c5c28e2",
        "productName": "CDB"
      }
    }]
python mongodb pymongo

1 answer

1

Good morning Luan. To make this query, is to use the Aggregation framework that the database offers you, with this you can filter inside the nested Documents.

Follow the query.

Objects from the bank:

[
  {
    "_id": "5d3fc8c3914297c7b9a3a9e5",
    "banco": "banco 1",
    "investimentos": [
      {
        "bank": "banco1",
        "risk": "Conservador",
        "expiryDate": "2021-10-04",
        "tax": "1.02",
        "discriminator": "investment",
        "productName": "LCI"
      },
      {
        "bank": "banco1",
        "risk": "Conservador",
        "expiryDate": "2020-06-24",
        "tax": "0.75",
        "discriminator": "investment",
        "productName": "Fundo DI"
      },
      {
        "bank": "banco1",
        "risk": "Conservador",
        "tax": "0.04",
        "discriminator": "investment",
        "id": "259ad8ac-57b7-4d33-8e75-46cf5c5c28e3",
        "aniversary": "30",
        "productName": "Poupanca"
      }
    ]
  },
  {
    "_id": "5d3fcb4c914297c7b9a3a9e6",
    "banco": "banco2",
    "investimentos": [
      {
        "bank": "banco2",
        "risk": "Conservador",
        "expiryDate": "2020-06-24",
        "tax": "0.80",
        "discriminator": "investment",
        "id": "73db503f-c780-448c-a6a8-05d2837ff6ff",
        "redemptionDate": "D+1",
        "productName": "Fundo DI"
      },
      {
        "bank": "banco2",
        "risk": "Conservador",
        "expiryDate": "2020-12-17",
        "tax": "0.98",
        "discriminator": "investment",
        "id": "54e01515-dc7f-470f-8f00-8603c8f00686",
        "productName": "LCA"
      },
      {
        "bank": "banco2",
        "risk": "Conservador",
        "expiryDate": "2021-08-05",
        "tax": "1.0",
        "discriminator": "investment",
        "id": "259ad8ac-57b7-4d33-8e75-46cf5c5c28e2",
        "productName": "CDB"
      }
    ]
  }
]

Query:

var pipeline = [
    {
        $match:
        {
            'investimentos.bank':'banco1'
        }
    },
    {
        $project:{
            banco: 1,
            investimentos:{
                $filter{
                    'input': '$investimentos',
                    'as': 'item',
                    'cond': {
                        $ne: ['$$item.productName', 'Poupanca']
                    }
                }
            }
        }
    }
];

db.collection.aggregate(pipeline)

For more information, see:

https://docs.mongodb.com/manual/aggregation/

https://docs.mongodb.com/manual/reference/operator/aggregation/filter/

https://university.mongodb.com/

To test the code without having a proper database:

https://mongoplayground.net/

Doubts, I am available.

Browser other questions tagged python mongodb pymongo

You are not signed in. Login or sign up in order to post.