Loopback-next: How can i use mongodb aggregate with lb4?

[PandoraTV]

How can i use mongodb find or aggregate command with lb4?

Where is example?

[half-blood-programmer]

Something like:

@get('/notifications', {
    responses: {
      '200': {
        description: 'Array of Notification model instances',
        content: {
          'application/json': {
            schema: {type: 'array', items: {'x-ts-type': Notification}},
          },
        },
      },
    },
  })
  async find(
    @param.query.object('filter', getFilterSchemaFor(Notification)) filter?: Filter,
  ): Promise<Notification[]> {

    const notificationCollection = (this.notificationRepository.dataSource.connector as any).collection("Notification");

    return await notificationCollection
      .aggregate([
        {$match: {user_id: 13}},
        {$group: {
            _id:     '$group',
            id:      {$max: "$_id"},
            read:    {$max: "$read"},
            kind:    {$first: "$kind"},
            created: {$max: "$created"},
            creator: {$first: "$creator"},
            item:    {$first: "$item"},
            count:   {$sum: 1}
          }},
        {$project: {
            _id:     0,
            id:      1,
            read:    1,
            kind:    1,
            created: 1,
            creator: 1,
            item:    1,
            count:   1,
            group:   '$_id'
          }},
        {$sort: {created: -1, group: -1}},
        {$skip: ((1 - 1) * 1)},
        {$limit: 2}
      ]).get();
  }

[hwangjundong]

Thank you @octisfall.

[javiersantossanchez]

Thank you @octisfall I had the same question and that solution works as I need

[onkelfunny]

@get('/test', {
    responses: {
      '200': {
        description: 'Orders',
        content: {
          'application/json': {
            schema: {type: 'array', items: {'x-ts-type': Order}},
          },
        },
      },
    },
  })
  async test(
  ): Promise<Order[]> {
    const orderCollection = (this.customerRepository.dataSource.connector as any).collection("Order");
    return await orderCollection
      .aggregate([
        {$limit: 2}
      ]).get();
  }

Unhandled error in GET /test: 500 Error: MongoDB connection is not established

What is wrong?

[half-blood-programmer]

@onkelfunny I have same error with first request, further it works fine. Don't know related it with my solution or not

[onkelfunny]

@octisfall its not working if the first request is the aggregation request. when I call first another url it works.

[half-blood-programmer]

@onkelfunny please create a new issue, maybe loopback contributers will help. I'm too interesting with this

[vanildo]

Thank you @octisfall. I solved the first access issue using this code:
if (!this.dataSource.connected) { await this.dataSource.connect(); } return (await this.dataSource.connector as any).collection('Dialog');

I hope it helps.

[sureshkodur]

Hi I found, 500 TypeError: Cannot read property 'settings' of undefined
at MongoDB.collectionName, while using the above query. I'm using
"loopback-connector-mongodb": "^4.1.0",
below is the code.

async create(
    @requestBody() loanClosingStatus: LoanClosingStatus[],
  ): Promise<any> {
    let repayments = await this.loanClosingStatusRepository.createAll(
      loanClosingStatus,
    );
    console.log('repayments', repayments);
    console.log(this.loanClosingStatusRepository.dataSource.connected); // false
    if (!this.loanClosingStatusRepository.dataSource.connected) {
      console.log('in if condition... initialization not complete');
      await this.loanClosingStatusRepository.dataSource.connect();
    }
    console.log(this.loanClosingStatusRepository.dataSource.connected); //true
    const _loanMappingCollection = (this.loanClosingStatusRepository.dataSource
      .connector as any).collection('LoanRequestMappings');  // it returns 500 error.

    console.log(_loanMappingCollection);
    const _loanRequestCollection = (this.loanClosingStatusRepository.dataSource
      .connector as any).collection('LoanRequests');
    let requestIDs = await _loanRequestCollection.find(
      {customerID: 'adasdasdasd'},
      {input_requestID: 1},
    );

    console.log('requestIDs:', requestIDs);

    let lmData = await _loanMappingCollection.aggregate([
      {
        $lookup: {
          from: 'LoanRequests',
          localField: 'RequestID',
          foreignField: 'input_requestID',
          as: 'AvailableLoanRequests',
        },
      },
      {$unwind: '$AvailableLoanRequests'},
      {
        $match: {
          $or: [
            {RequestID: '152216107695585237'},
            {RequestID: '152216076180212068'},
          ],
        },
      },
      {
        $lookup: {
          from: 'LoanClosingStatus',
          localField: 'LoanID',
          foreignField: 'LoanID',
          as: 'LoanRepayment',
        },
      },
      {$unwind: '$LoanRepayment'},
      {$project: {_id: 0}},
      {$out: 'tempData'},
    ]);

    console.log('lmData:', lmData);

    let paymentData = await this.getPayments(JSON.stringify(repayments));
    console.log('paymentData:', paymentData);
    return paymentData;
  }

Error:
Unhandled error in POST /closingStatus: 500 TypeError: Cannot read property 'settings' of undefined
at MongoDB.collectionName (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/node_modules/loopback-connector-mongodb/lib/mongodb.js:343:18)
at LoanClosingStatusController.create (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/dist/controllers/loan-closing-status.controller.js:47:24)
at value_promise_1.transformValueOrPromise.args (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/node_modules/@loopback/context/dist/resolver.js:208:41)
at Object.transformValueOrPromise (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/node_modules/@loopback/context/dist/value-promise.js:227:16)
at Object.invokeMethod (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/excelrate-v2/node_modules/@loopback/context/dist/resolver.js:203:28)
at ControllerRoute.invokeHandler (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/node_modules/@loopback/rest/dist/router/controller-route.js:78:32)
at process._tickCallback (internal/process/next_tick.js:68:7)

[sureshkodur]

Hi @octisfall , My code is for aggregation is below

async create(
    @requestBody() loanClosingStatus: LoanClosingStatus[],
  ): Promise<any> {
    // let repayments = await this.loanClosingStatusRepository.createAll(
    //   loanClosingStatus,
    // );

    console.log(
      'Connected:',
      this.loanClosingStatusRepository.dataSource.connected,
    );
    if (!this.loanClosingStatusRepository.dataSource.connected) {
      console.log('Waiting for connection...');
      await this.loanClosingStatusRepository.dataSource.connect();
    }
    console.log(
      'Connected:',
      this.loanClosingStatusRepository.dataSource.connected,
    );
    console.log('Getting loanMappingCollection...');
    const _loanMappingCollection = (this.loanClosingStatusRepository.dataSource
      .connector as any).collection('LoanRequestMappings');

    console.log(_loanMappingCollection);

     return await _loanMappingCollection
      .aggregate([
        {
          $lookup: {
            from: 'LoanRequests',
            localField: 'RequestID',
            foreignField: 'input_requestID',
            as: 'AvailableLoanRequests',
          },
        },
        {$unwind: '$AvailableLoanRequests'},
        {
          $match: {
            $or: [
              {RequestID: '152216107695585237'},
              {RequestID: '152216076180212068'},
            ],
          },
        },
        {
          $lookup: {
            from: 'LoanClosingStatus',
            localField: 'LoanID',
            foreignField: 'LoanID',
            as: 'LoanRepayment',
          },
        },
        {$unwind: '$LoanRepayment'},
        {$project: {_id: 0}},
        {$out: 'tempData'},
      ])
      .get();
  }

I'm getting the below error.

--- Database in local Mode ---
{ name: 'mongoDs',
connector: 'mongodb',
url: 'mongodb://localhost:27017/xxdb' }
Server is running at http://[::1]:3000
Connected: false
Waiting for connection...
Connected: true
Getting loanMappingCollection...
Unhandled error in POST /closingStatus: 500 TypeError: Cannot read property 'settings' of undefined
at MongoDB.collectionName (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/node_modules/loopback-connector-mongodb/lib/mongodb.js:341:18)
at MongoDB.collection (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/node_modules/loopback-connector-mongodb/lib/mongodb.js:356:29)
at LoanClosingStatusController.create (/Volumes/Projects/WORKSPACE/suresh-koduri/loopback/projectlb4/dist/controllers/loan-closing-status.controller.js:45:24)
at process._tickCallback (internal/process/next_tick.js:68:7)

[likexoo]

@sureshkodur , hi have you found a solution? Here is my code:

• run aggregate command with db object

if (!repository.dataSource.connected) await repository.dataSource.connect();
repository.dataSource.connector!.client.db(repository.dataSource.settings.database).command({
    {
        aggregate: "Transaction",
        pipeline: [
            { "$match": {} }
        ],
        cursor: {}
    }
}, {}, (error: any, documents: any) => {
    //...
});

• run aggregate() with collection object

if (!repository.dataSource.connected) await repository.dataSource.connect();
repository.dataSource.connector!.client.db(repository.dataSource.settings.database).collection(repository.modelClass.name).aggregate([
    { "$match": {} }
], {}).toArray((error: any, documents: any[]) => {
    //...
});

I also tried to run your code, but got the same error as you.

[sureshkodur]

@likexoo , Yes. I found solution. I did exact what you did, and it worked

[bajtos]

See also my proposal in https://github.com/strongloop/loopback-next/issues/2807#issuecomment-487590905, it offers slightly different code.

I opened a new issue to describe the more generic feature we are looking for - the ability to execute raw MongoDB queries. See https://github.com/strongloop/loopback-next/issues/3342

It would be great if you can up-vote the issue by adding a :+1: reaction (not a comment please, see https://github.blog/2016-03-10-add-reactions-to-pull-requests-issues-and-comments/), that way we will know how many people are asking for this particular feature.

I am closing this issue as a duplicate.

[VigneshMeem]

repository.modelClass.name
Instead Mymodel.modelName
Woked for me
code

`

const collection = await // eslint-disable-next-line @typescript-eslint/no-explicit-any
(this.userdataRepository.dataSource.connector as any).collection(
  Userdata.modelName,
);

const data: Userdata = await collection
  .aggregate([
    {$match: {userid: id}},
    {$sort: {timestamp: -1}},
    {$limit: 1},
  ])
  .get();`