How to search an array of objects and return key value pairs using an array as a reference?

Working with Data
,
1

Hello,

I have a collection which contains an array of objects, each containing a date and a price. I would like to be able to run an aggregate query that passes in an array of dates, check if any of those dates are contained within the object in the array, and if so, return the corresponding price. For example,

Sample data structure:

[
       {
        "key": 1
        pricing: [
        {
             "date":"2022-09-09T16:00:00.000+00:00",
             "price": 100
        },
       {
              "date":"2022-09-10T16:00:00.000+00:00",
              "price": 100
       },
       {
             "date":"2022-09-11T16:00:00.000+00:00",
             "price": 100
       },
    ]
  },
]

I’m not sure how to search an object in an array with an array, and if it exists project the value of another key. I’ve been trying something like this

const dates = [
    '2022-09-07T16:00:00.000+00:00',
    '2022-09-08T16:00:00.000+00:00',
    '2022-09-09T16:00:00.000+00:00',
]

{
    '$match': {
       "pricing.date" : {$in : dates}
    }
},
{
    '$project': {
       "pricing.date" : 1
       "pricing.price" : 1
    }
}

The data I would like back would be something like this

[
    {date: 2022-09-09T16:00:00.000+00:00 ,price: 100}
]

I have also tried $elemMatch like this

{
    "$match": {
        "pricing.date": {
              $elemMatch: {
                  $in: dates,
              },
        }
    }
}

I have a non-working :rofl: Playground here: https://mongoplayground.net/p/hyk-oYEF6RZ

Any help would be very much appreciated!
Cheers,
Matt

2

Hi @Matt_Heslington1 ,

You don’t specifically need an aggregation to do that query. You can use positional projecting like the following query:

 db.collection.find({
  "pricing.date": {
    "$in": [
    '2022-09-07T16:00:00.000+00:00',
    '2022-09-08T16:00:00.000+00:00',
    '2022-09-09T16:00:00.000+00:00',
]
  }
},
{
  "pricing.$": 1
})

Now you have to make sure that if you comparing strings as dates you either have them in strings in your documents and then they have to exactly match a string (all chars), or use date formats in both collection and query array.

Thanks
Pavel

3

Hello Pavel,

Thank you for your help. That works, but have should have been more specific in my question - I need to pass in an ‘_id’, so for a specific record:

db.collection.find(
            {
                _id: 'H8Se4O_LyGYFmMatcjK6_'
            },
            {
                'pricing.date': {
                    $in: ['2022-09-07T16:00:00.000+00:00', '2022-09-08T16:00:00.000+00:00', '2022-09-09T16:00:00.000+00:00'],
                },
            },
            {
                'pricing.$': 1,
            }
        )

But I believe you can only pass in two arguments to a ‘find’ query like this, so adding the third ‘_id’ argument breaks it. Just a quick question too, the way we’re doing it at the moment the date fields are being passed as strings as you said - how do we pass them as dates?

Thanks again!
Matt

4

Hi @Matt_Heslington1 ,

Nope you can add several fields in the first query object so it will peform an “and” condition across the fields, you can also convert strings to dates via the specific driver, with shell just wrap them in ISODate() functions

db.collection.find(
            {
                 _id: 'H8Se4O_LyGYFmMatcjK6_',
                'pricing.date': {
                    $in: [ISODate('2022-09-07T16:00:00.000+00:00'),ISODate( '2022-09-08T16:00:00.000+00:00'), ISODate('2022-09-09T16:00:00.000+00:00')],
                },
            },
            {
                'pricing.$': 1,
            }
        )
1 Like
5

Hi Pavel,

That’s perfect. Thank you so much, you’ve been a great help. Much appreciated.
Have a great day,
Matt

6

Hi Pavel,

Apologies, I did a false test and thought it was working when it wasn’t. It returns the first matched record, but only the first, not all of the matches. In the updated Playground, the query should match two dates, Sep 09 and Sep 10, but it’s only returning data for Sep 09 - I’d love it to be able to return data like below, ie. one more record.

[
  {
    "_id": "abc",
    "pricing": [
      {
        "date": "2022-09-09T16: 00: 00.000+00: 00",
        "price": 100
      },
      {
        "date": "2022-09-10T16: 00: 00.000+00: 00",
        "price": 100
      }
    ]
  }
]

I can’t understand why it’s not working, as everything looks good according to the docs

Cheers,
Matt

7

Hi @Matt_Heslington1 ,

in that case you probably need an aggregation with a $filter stage to only match elements that have the criteria:

db.collection.aggregate([
  {
    $match: {
      "_id": "abc",
      "pricing.date": {
        "$in": [
          "2022-09-08T16: 00: 00.000+00: 00",
          "2022-09-09T16: 00: 00.000+00: 00",
          "2022-09-10T16: 00: 00.000+00: 00",
          
        ]
      }
    }
  },
  {
    "$addFields": {
      "pricing": {
        "$filter": {
          "input": "$pricing",
          "as": "price",
          "cond": {
            $in: [
              "$$price.date",
              [
                "2022-09-08T16: 00: 00.000+00: 00",
                "2022-09-09T16: 00: 00.000+00: 00",
                "2022-09-10T16: 00: 00.000+00: 00"
                
              ]
            ]
          }
        }
      }
    }
  }
])

Let me know if that works?

Ty

1 Like
8

Yes, it works! Great, thank you, Pavel, you’ve been a star!

9

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.