SetIntersection with an array provided in parameter during Aggregation in Spring Data MongoDB

Working with Data
#1

Hi there,

I’m coding for a requirement using Aggregation Framework to detect whether an array as a field of a parent nested array element contains at least one common element from an external array provided through the method parameter String[] externalArray. The schema of the document is like the following:

{
"fields_v" : [ {
     "value" : [ txt ]
} ]
}

I attempted to use array (as set) intersection and check if the resulting set is empty to resolve it. But as I had looked through the document, it seems the SetIntersection operator can only handle intersections of arrays already as fields, no way to access an external array. Is there any workaround or did I miss something? Any help is much appreciated, thanks!

#2

Welcome, @Alex_Tsang!

$setIntersection operator is not necessary for that.
Let’s assume, you have the following dataset:

db.test1.insertMany([
  {
    "_id": 1,
    "rootArray" : [
      {
        "nestedArray" : [ 'd1', 'd2' ]
      }
    ]
  },
  {
    "_id": 2,
    "rootArray" : [
      {
        "nestedArray" : [ 'd1', 'd3' ]
      }
    ]
  },
  {
    "_id": 3,
    "rootArray" : [
      {
        "nestedArray" : []
      }
    ]
  },
  {
    "_id": 4,
    "rootArray" : []
  }
]);

Then, you can $match documents, that have one or more values from our function arguments:

// mongo shell example
function hasInNestedArray(values) {
  return db.test1.aggregate([
    {
      $match: {
        'rootArray.nestedArray': {
          $in: values,
        }
      }
    }
  ]).pretty();
}

Usage examples:

hasInNestedArray(['d1']) // will match both documents
hasInNestedArray(['d3']) // will match only second document (_id=2)

You should better take some courses to educate yourself to use MongoDB.
I recommend to start with the very basic one.

#3

Hi Slava,

Thank you for your quick and neat answer, all I need is the $in operator, who can imagine it implies a secondary function for array intersection simply by its name? And thank you also for your beautifully formatted code to convey my original question for I was in a haste. Anyway, if you are still available, please allow me to re-use your pretty code,

db.test1.insertMany([
  {
    "_id": 1,
    "rootArray" : [
      {
        "nestedArray" : [ 20, 23, 34 ]
      }
    ]
  },
  {
    "_id": 2,
    "rootArray" : [
      {
        "nestedArray" : [ 31, 34, 56 ]
      }
    ]
  },
  {
    "_id": 3,
    "rootArray" : [
      {
        "nestedArray" : [56, 57, 58]
      }
    ]
  },
  {
    "_id": 4,
    "rootArray" : []
  }
]);

Now this time, of a second real requirement, we supply a lower bound and an upper bound, do we still have a basic solution to spot all the nested arrays containing at least one element falling into the range?

// mongo shell example
function hasInNestedArray(min_value, max_value) {

}
#4

This function should do the thing:

function hasInRangeInNestedArray(lowerBound, upperBound) {
  return db.test1.aggregate([
    {
      $match: {
        'rootArray.nestedArray': {
          $gte: lowerBound,
          $lte: upperBound,
        }
      }
    }
  ]).pretty();
}

The solution supports inclusive range edges only. If you need exclusive ones - use $gt and $lt instead.

#5

Hi Slava,

Unfortunately I’m afraid your code won’t work as expected. According to the array comparison rules,

With arrays, a less-than comparison or an ascending sort compares the smallest element of arrays, and a greater-than comparison or a descending sort compares the largest element of the arrays.

Let’s take one of the above objects as an example,

  {
    "_id": 2,
    "rootArray" : [
      {
        "nestedArray" : [ 31, 34, 56 ]
      }
    ]
  }

And I offer a lower bound of 50 and an upper bound of 100, “rootArray.0.nestedArray” is less than 100 because its maximum value 56 is less than it, but this array won’t be greater than 50 because its minimum value 31 is still less than it, so this will result in this whole array missed out from a match, despite it has an element of 56 falling into my specified range and should be accepted.

Of course with Spring Data we can easily swap the $and condition with an $or

 criteria.orOperator(								
		where("rootArray.nestedArray").gte(lowerBound), 
		where("rootArray.nestedArray").lte(upperBound));

But this won’t help either if we offer a range of [80, 100], this array will be checked as a match but it actually doesn’t have any elements inside the range.

Any brighter ideas?

#6

You will have to take a look at $elemMatch.

https://docs.mongodb.com/manual/reference/operator/query/elemMatch/

#7

Thanks for inspiration.

criteria.and("parentArray.nestedArray").elemMatch(
	new Criteria().andOperator(
			new Criteria().gte(lowerBound),
			new Criteria().lte(upperBound)));
1 Like