Find() method to query substring of a field value in array not equal to a field value

[emmanuel_bernard]

Hi there,

Here is a document from my collection.

{
optionId: ‘18270562735’,
sizes: [
{ sku: ‘1827056273534’ },
{ sku: ‘1827056273536’ },
{ sku: ‘1827056273538’ },
{ sku: ‘1827056273540’ },
{ sku: ‘1827056273542’ },
{ sku: ‘1827056273544’ },
{ sku: ‘1827056273546’ },
{ sku: ‘1827056273548’ },
{ sku: ‘1126083251150’ },
{ sku: ‘1126083251152’ }
]
}

I’d like to find all documents where the substring of sku (length 11) in the “sizes” array is not equal to “optionId” value.

I succed with aggregate() operation.
Is there any way to do so with a find() operation ?

Such a query is wrong

find({‘sizes.sku’:{$regex:/’$optionId.*’/}})

[emmanuel_bernard]

I tried something that make an error

{$expr:{$not:{$in:[{$substr:[’$sizes.sku’,0,11]},[’$optionId’]]}}}

[Asya_Kamsky]

There are a number of ways to do this, all of them require somehow trimming an array of sku’s to just first 11 characters, which you can do with $map:

{$map: { input: "$sizes.sku", in: {$substrCP:["$$this", 0, 11] } } }

or

{$map: { input: "$sizes", in: {$substrCP:["$$this.sku", 0, 11] } } }

Now that you have an array [ "18270562735", "18270562735", "18270562735", "18270562735", "18270562735", "18270562735", "18270562735", "18270562735", "11260832511", "11260832511" ] you can use $in to check if $optionId is there:

db.foo.find( {$expr: {$in: [ 
                "$optionId", 
                {$map: { input: "$sizes", in: {$substrCP:["$$this.sku", 0, 11] } } } 
] } } )

Asya

[emmanuel_bernard]

Hi Asya,

Thanks a lot for your answer.

I didn’t know it was possible to use $map in find() operator.
Is there a full documentation to find all behaviors ?

[Asya_Kamsky]

$expr allows use of all aggregation expressions.