Concatenate all sub-items within a JSON object

Question

Concatenate all sub-items within a JSON object

I have 2 Objects like this :

[
  {
    _id: ObjectId("62990f96345ef9001d9f2dfe"),
    deletedAt: null,
    expiredAt: ISODate("2022-06-05T19:29:26.746Z"),
    dataBarang: [
      {
        vendor: ObjectId("6215dd91139c99003fe4c7cd"),
        currency: ObjectId("61f750b56ea409001e521043"),
        modelId: 14,
        usdConvertion: 1,
        price: 15,
        leadTime: null,
        quantity: 2
      }
    ],
    cartItems: [
      ObjectId("62990f77345ef9001d9f2dfd")
    ],
    reqNo: "f0124ae0-1072-4f18-ab90-a7d2d784e634",
    reqDate: ISODate("2022-06-03T00:00:00.000Z"),
    updatedAt: ISODate("2022-06-02T19:29:30.140Z"),
    createdAt: ISODate("2022-06-02T19:29:26.747Z"),
    __v: 0,
    pickUpDate: ISODate("2022-06-03T00:00:00.000Z"),
    transactionDate: ISODate("2022-06-03T00:00:00.000Z")
  },
  {
    _id: ObjectId("62990fe9345ef9001d9f2e03"),
    deletedAt: null,
    expiredAt: ISODate("2022-06-05T19:30:49.081Z"),
    dataBarang: [
      {
        vendor: ObjectId("6215dd91139c99003fe4de15"),
        currency: ObjectId("61f750b56ea409001e521043"),
        modelId: 14,
        usdConvertion: 1,
        price: 64.82,
        leadTime: 14,
        quantity: 1
      }
    ],
    cartItems: [
      ObjectId("62990fcc345ef9001d9f2e02")
    ],
    reqNo: "c2b378ad-87fd-4db4-96b8-6812cb1f2229",
    reqDate: ISODate("2022-06-03T00:00:00.000Z"),
    updatedAt: ISODate("2022-06-02T19:30:51.269Z"),
    createdAt: ISODate("2022-06-02T19:30:49.082Z"),
    __v: 0,
    pickUpDate: ISODate("2022-06-03T00:00:00.000Z"),
    transactionDate: ISODate("2022-06-03T00:00:00.000Z")
  }
]

And what I want to achieve is, to Sum the price of dataBarang for each Object every transactionDate. Please take note that each Objects able to have multiple dataBarang.

I tried with my latest code but still not showing something :

    this.myDBservices.aggregate([])
          .match({
            transactionDate: {
              $gte: '2023-01-01',
              $lte: '2023-01-20'
            }
          })
          .group({
            _id: { $dateFromParts:{year:{$year:"$transactionDate"}, month:{$month:"$transactionDate"}, day:{$dayOfMonth : "$transactionDate" }} },
            date: { $first: "$transactionDate" },
            total: { $sum: 1 },
            totalPrice : {
              $map: {
                $sum: "$$dataBarang.quantity"
              }
            }
          });

Maybe someone want to help me what should It be? really appreciate for every help. Thank you

typescript mongodb mongodb-query aggregation-framework

Answer 1

Answer №1

There seems to be several issues with your current approach.

The date fields in your schema are likely set as type date, but you are trying to match them with strings in your initial $match, causing no documents to be matched.
dataBarang.quantity is stored as an array, requiring two `$sum operations for accurate summing of the field.
There are various typos and syntax errors present that will require debugging on your end.

Here is a basic demonstration of how you should use $sum on the dataBarang.quantity field:

db.collection.aggregate([
  {
    $match: {
      transactionDate: {
        $gte: ISODate("2021-01-01"),
        $lte: ISODate("2023-01-20")
      }
    }
  },
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: "$transactionDate",
          unit: "day"
        }
      },
      totalPrice: {
        $sum: {
          $sum: "$dataBarang.price"
        }
      }
    }
  }
])

Mongo Playground

Answer 2

There seems to be several issues with your current approach.

The date fields in your schema are likely set as type date, but you are trying to match them with strings in your initial $match, causing no documents to be matched.
dataBarang.quantity is stored as an array, requiring two `$sum operations for accurate summing of the field.
There are various typos and syntax errors present that will require debugging on your end.

Here is a basic demonstration of how you should use $sum on the dataBarang.quantity field:

db.collection.aggregate([
  {
    $match: {
      transactionDate: {
        $gte: ISODate("2021-01-01"),
        $lte: ISODate("2023-01-20")
      }
    }
  },
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: "$transactionDate",
          unit: "day"
        }
      },
      totalPrice: {
        $sum: {
          $sum: "$dataBarang.price"
        }
      }
    }
  }
])

Mongo Playground

Concatenate all sub-items within a JSON object

Answer №1

Similar questions

String Compression - Number of Elements

The specified property ID is not found in the User type

Designing a MongoDB Schema for Instant Messaging Apps

Struggling to enter the command `zip` and accurately anticipating when inference fails in overloaded functions involving generics

Error message shows explicit Typescript type instead of using generic type name

Adjust the size of an Angular component or directive based on the variable being passed in

Migration unsuccessful due to incompatible peer dependencies detected - Updating Angular to Version 12 was not successful

Is there a method to incorporate absolute paths in SCSS while working with Vite?

Exploring ways to access elements within shadow-root (open) in Angular using SVG.js

The Angular CLI suddenly decided to stop providing me with useful lines (without sourcemaps) in the browser console, but interestingly the terminal continues

React typescript - Error: Type 'boolean' is not compatible with the expected type

Tips for incorporating the closeAutocomplete function into ng4-geoautocomplete

Creating a TypeScript interface that has keys determined by the elements in an array

Determining changes in an object with Angular 2 and Ionic 2

What is the best way to assign an index signature to a plain object?

What could be the reason for mongoose.find producing an empty Array?

Swap out the selector of an Ionic2 component with its contents

Only one argument is accepted by the constructor of NGRX data EntityServicesBase

The Best Way to Filter Mongo Documents Using Nested Objects

The guidelines specified in the root `.eslintrc.json` file of an NX workspace do not carry over to the project-level `.eslintrc.json` file