Question 299751 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Retrieve JSON documents with a certain month and year in MongoDB

I am trying to retrieve some documents from a collection in my MongoDB instance.
Here you are the entire collection:

[
  {
    _id: ObjectId("65708c6237b62067296a398f"),
    date: ISODate("2020-09-15T04:07:05.000Z"),
    description: 'playstation',
    category: 'miscellaneous',
    cost: 499,
    users: [ { username: 'Giacomo', amount: 499 } ]
  },
  {
    _id: ObjectId("6570950cecb7eb1b4b868409"),
    date: ISODate("2020-09-15T04:07:05.000Z"),
    description: 'tennis court',
    category: 'sport',
    cost: 100,
    users: [
      { username: 'prova', amount: 50 },
      { username: 'Giacomo', amount: 50 }
    ]
  },
  {
    _id: ObjectId("6570953aecb7eb1b4b86840a"),
    date: ISODate("2023-02-09T03:12:15.012Z"),
    description: 'netflix subscription',
    category: 'entertainment',
    cost: 100,
    users: [ { username: 'prova', amount: 10 } ]
  }
]

At the moment, I can retrieve documents with a certain year as a part of the value of date with the following query:

  db
    .collection("expenses")
    .find({
      "users.username": "desired_username",
      $expr: { $eq: [{ $year: "$date" }, 2020] },
    })

As the next step, I want to retrieve documents that have not only a certain year but a certain month too (e.g. september, 2020 should return the first two documents). I don’t know how to use two different expressions in the query.

Can anyone help me to solve the problem?

Thank you in advance for your patience.

Tags:

2

Answers


  1. 0

    in simplest terms you could have something like

    db.collection("expenses").find({
  "users.username": "desired_username",
  $expr: {
    $and: [
      { $eq: [{ $year: "$date" }, 2020] },
      { $eq: [{ $month: "$date" }, 9] }
    ]
  }
})

    playground

    or if you want more fancy you could go with something like this with date to string formatting (need mongo 7)

    db.collection("expenses").find({
  "users.username": "desired_username",
  $expr: {
      { $eq: [{ $dateToString: { format: "%B, %Y", date: "$date" } }, "september, 2020"] }  
  }
})

    this will not work in mongoplayground since it runs on a lower mongo version

    or something that might run on older versions like

    db.collection("expenses").find({
  "users.username": "Giacomo",
  $expr: {
    $and: [
      { $eq: [{ $year: "$date" }, 2020] },
      {
        $eq: [
          {
            $arrayElemAt: [
              ["", "january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december"],
              { $month: "$date" }
            ]
          },
          "september"
        ]
      }
    ]
  }
})

    playground

    okay one last just for fun but i guess this is too much making the query hard to understand at times ( this is exactly the longer version of what is shown in 2nd approach)

    db.collection("expenses").find({
  "users.username": "Giacomo",
  $expr: {
    $eq: [
      {
        $concat: [
          {
            $arrayElemAt: [
              ["", "january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december"],
              { $month: "$date" }
            ]
          },
          ", ",
          { $toString: { $year: "$date" } }
        ]
      },
      "september, 2020"
    ]
  }
})

    playground

    Login or Signup to reply.
  2. 0

    A simple approach which you could modify is to leverage the $gte and $lte comparison operators to specify a start and end date from which to narrow your query. Below we have greater than or equal to 1st Sept 2020 but less than or equal to 30th Sep 2020:

    db.collection("expenses").find({
   'users.username': 'desired_username',
   'date': {
      $gte: ISODate("2020-09-01"), 
      $lte: ISODate("2020-09-30")
   }
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search