How to write projection to get nested object only from collection schema

Working with Data
,
1

Hi,

I have a schema like this

{
       name : "A",
	outerArr : [
		{
			outerId : 1,
			innerArr : [
				{
					 { date: '2023-08-1', type: 'Normal'},
					 { date: '2023-08-2', type: 'Normal'},
					 { date: '2023-08-3', type: 'Normal'}
				}
			]
		},
		{
			outerId : 2,
			innerArr : [
				{
					 { date: '2023-08-1', type: 'Normal'},
					 { date: '2023-08-2', type: 'Normal'},
					 { date: '2023-08-3', type: 'Normal'}
				}
			]
		},
		{
			outerId : 3,
			innerArr : [
				{
					 { date: '2023-08-1', type: 'Normal'},
					 { date: '2023-08-2', type: 'Normal'},
					 { date: '2023-08-3', type: 'Normal'}
				}
			]
		}
	]
}

I want to find a document with inputs
name = “A” and outerId=1 and date= “2023-08-1”

Here is my query for the same

db.collection.find(
    { name: "abc", "outerArr.outerId": 1, "outerArr.innerArr.date": "2023-08-1" }
)

However, my intention is to get/retrieve only innerArr element matching date

{ date: ‘2023-08-1’, type: ‘Normal’}

But i am getting complete data since i do not have any filter or projection.

Please help in writing projection field to get only

{ date: ‘2023-08-1’, type: ‘Normal’}

2

A aggregation with $match, $unwind and repalce root operation could be the solution here.

  • $match to find just records with name: “ABC”
  • $unwind the outerArr data to get each item in an individual document
  • $match to filter out items that satisfy outerID criteria
  • $unwind the innerArr data to get each item in an individual document
  • $match to filter out items that satisfy date criteria
  • $replaceRoot to move the data within the innerArr field up to the root

Build the aggregation pipeline stage by stage so you can track whats going on.

You may also want to think about changing the date to a date object as opposed to string…

3

Beautiful!
Thanks a lot John. Enjoyed the process of building pipeline and observing outcome in each stage. Great to see its working.

One question

  1. Does aggregation degrades query performance ? Example in this case.
  2. Thanks for suggesting date to make it date ISO object instead of string. Could you please share your motive behind this ?
4

Should not in this case as the primary match should identify the actual document and then you are just manipulating the details of one document. Of course the alternative is to do the extraction in code as opposed to on the server, which probably is more of an overhead.

Date are actually stored as numbers, so the storage needs are much smaller than a string. Its also much faster to compare to numbers than two strings so you get a boost there.

2 Likes