Join two collections array of object match

Working with Data
1

I have two collections “datasets” and “users” both having an array of object fields.

datasets inside stateHistory array object having date fields.
users inside some of the users have a prices array of object date fields
I need to join stateHistory.date === prices.date fields

I need an output of

"datasets": [
        {
          "eyeSize": 54,
          "bridgeSize": 15,
          "templeLength": 135,
          "colorCode": "F010",
          "colorDescription": "braun, rose gold",
		  "cgPrices": 200,
          "stateHistory": [
            {
              "state": "scanning",
              "date": "2022-02-22T13:06:13.493+00:00",
              
            },
            {
              "state": "scanned",
              "date": "2022-02-18T13:06:13.493+00:00",
              
            },
            {
              "state": "reconstructing",
              "date": "2022-02-16T13:06:13.493+00:00",
              
            }
          ]
        },
        {
          "eyeSize": 54,
          "bridgeSize": 15,
          "templeLength": 135,
          "colorCode": "F011",
          "colorDescription": "beige, silber",
          "stateHistory": [
            {
              "state": "scanning",
              "date": "2022-03-22T13:06:13.493+00:00",
              
            },
            {
              "state": "scanned",
              "date": "2022-03-18T13:06:13.493+00:00",
              
            },
            {
              "state": "reconstructing",
              "date": "2022-03-16T13:06:13.493+00:00",
              
            }
          ]
        }
      ]

my code: Mongo playground

3

Hello :wave: @Aravinth_E,

Welcome to the MongoDB Community forums :sparkles:

Let me re-iterate the problem statement to understand it better.

So, you have a users collections, which is:

[
    {
      "email": "test@mueller.com",
      "firstName": "Gerd",
      "lastName": "Müller",
      "role": "Admin",
      "isActive": true,
      "accountId": 12345,
      "prices": [
        {
          "date": "2022-03-22T13:06:13.493+00:00",
          "price": 95
        },
        {
          "date": "2022-02-16T13:06:13.493+00:00",
          "price": 105
        }
      ]
    },
    {
      "email": "tobias.noell@zeiss.com",
      "firstName": "Tobias",
      "lastName": "Noell",
      "role": "SuperAdmin",
      "isActive": true,
      "accountId": 32661,
      
    }
  ]

And you have another collection named datasets, which looks like:

[
    {
      "eyeSize": 54,
      "bridgeSize": 15,
      "templeLength": 135,
      "colorCode": "F010",
      "colorDescription": "braun, rose gold",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493+00:00",
          
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493+00:00",
          
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493+00:00",
          
        }
      ]
    },
    {
      "eyeSize": 54,
      "bridgeSize": 15,
      "templeLength": 135,
      "colorCode": "F011",
      "colorDescription": "beige, silber",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493+00:00",
          
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493+00:00",
          
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493+00:00",
          
        }
      ]
    }
  ]

In both collections, you don’t have any _id, which is the default primary key for the documents in the collection.

Also, the output format you shared in the questions, seems very similar to the datasets collection document!! Are you sure you want the same output as the "datasets" collection? Or I’m missing something?

Also in the playground, you’re grouping on the_id basis, which doesn’t exist in the above-given schema.

Please let us know if you have any further questions.

Thanks,
Kushagra