Spring data Match and Filter Nested Array

Question

How can extract data from nested array ?

I want to extract the array item "values" where wind_speed parameter value is between vitRange.min and vitRange.max (same condition for twaRange and wind direction)

Data :

{
    "name" : "race"
    ,"polaire" : [
        {
            "voile" : "foc"
            , "matrice" :[
                {
                    "vitRange" : { "min" : 0, "max" : 4}
                    ,"twaRange" : { "min" : 0, "max" : 30}
                    ,"values" : [0, 0, 0, 2.4]
                },
                {
                    "vitRange" : { "min" : 4, "max" : 6}
                    ,"twaRange" : { "min" : 30, "max" : 33}
                    ,"values" : [0, 0, 2.4, 3.7]
                }
            ]
        },
        {
            "voile" : "spi"
            , "matrice" :[
                {
                    "vitRange" : { "min" : 0, "max" : 4}
                    ,"twaRange" : { "min" : 0, "max" : 30}
                    ,"values" : [0, 0, 0, 1.4]
                },
                {
                    "vitRange" : { "min" : 4, "max" : 6}
                    ,"twaRange" : { "min" : 30, "max" : 33}
                    ,"values" : [0, 0, 1.4, 2.2]
                }
            ]
        }
    ]
}

First approach :

Query query = new Query(
  Criteria.where("name").is(name)
  .andOperator(
    Criteria.where("polaire.voile").is(sail),
    Criteria.where("polaire.matrice.twaRange.max").lt(wind_direction),
    Criteria.where("polaire.matrice.twaRange.min").gte(wind_direction),
    Criteria.where("polaire.matrice.vitRange.max").lt(wind_speed),
    Criteria.where("polaire.matrice.vitRange.min").gte(wind_speed)
  )
);
query.fields().include("polaire.matrice.values");
Polaires data = mongoTemplate.findOne(query, Polaires.class);

2nd approach:

Criteria findPolaireCriteria = Criteria.where("name").is(name);
Criteria findValueCriteria = Criteria.where("polaire").elemMatch(Criteria.where("voile").is(sail))
      .andOperator(
            Criteria.where("polaire.matrice.twaRange").elemMatch(Criteria.where("max").lt(wind_direction)),
            Criteria.where("polaire.matrice.twaRange").elemMatch(Criteria.where("min").gte(wind_direction)),
            Criteria.where("polaire.matrice.vitRange").elemMatch(Criteria.where("max").lt(wind_speed)),
            Criteria.where("polaire.matrice.vitRange").elemMatch(Criteria.where("min").gte(wind_speed)));

BasicQuery query = new BasicQuery(findPolaireCriteria.getCriteriaObject(), findValueCriteria.getCriteriaObject());

query.fields().include("polaire.matrice.values");
Polaires data = mongoTemplate.findOne(query, Polaires.class);

Last approach: (cf. Query a document and all of its subdocuments that match a condition in mongodb (using spring))

Aggregation aggregation = newAggregation(
        match(Criteria.where("name").is(name)
                .and("polaire").elemMatch(Criteria.where("voile").is(sail))),
        project( "_id", "matrice")
            .and(new AggregationExpression() {
            @Override
            public DBObject toDbObject(AggregationOperationContext aggregationOperationContext ) {
                DBObject filter = new BasicDBObject("input", "$matrice")
                    .append("as", "result")
                    .append("cond",
                        new BasicDBObject("$and", Arrays.<Object> asList(
                                new BasicDBObject("$gte", Arrays.<Object> asList("$$result.vitRange.min", 0)),
                                new BasicDBObject("$lt", Arrays.<Object> asList("$$result.vitRange.max", 4))
                                )
                        )
                );
                return new BasicDBObject("$filter", filter);
            }
        }).as("matrice")
);

List<BasicDBObject> dbObjects = mongoTemplate.aggregate(aggregation, "collectionname", BasicDBObject.class).getMappedResults();

Or Another one...

List<AggregationOperation> list = new ArrayList<AggregationOperation>();
list.add(Aggregation.match(Criteria.where("name").is(name)));
list.add(Aggregation.unwind("polaire"));
list.add(Aggregation.match(Criteria.where("polaire.voile").is(sail)));
list.add(Aggregation.unwind("polaire.matrice"));
list.add(Aggregation.match(Criteria.where("polaire.matrice.twaRange").elemMatch(Criteria.where("max").lt(wind_direction))));
list.add(Aggregation.match(Criteria.where("polaire.matrice.twaRange").elemMatch(Criteria.where("min").gte(wind_direction))));
list.add(Aggregation.match(Criteria.where("polaire.matrice.vitRange").elemMatch(Criteria.where("max").lt(wind_speed))));
list.add(Aggregation.match(Criteria.where("polaire.matrice.vitRange").elemMatch(Criteria.where("min").gte(wind_speed))));
list.add(Aggregation.group("id", "polaire.matrice").push("polaire.matrice.values").as("values"));
list.add(Aggregation.project("polaire.matrice","values"));

TypedAggregation<Polaires> agg = Aggregation.newAggregation(Polaires.class, list);
List<BasicDBObject> dbObjects = mongoTemplate.aggregate(agg, "collectionname", BasicDBObject.class).getMappedResults();

Turn around again and again on the forum but none of them help me. The issue is probably on working to the json structure (adapt it to easily request) ?

Thanks

See [`$elemMatch`](https://docs.mongodb.com/manual/reference/operator/query/elemMatch/). This is the operation you want for matching multiple criteria within an array element. Otherwise the conditions are applied to **all** possible array positions. You want a "singular" element to match the conditions. Or at least all of those conditions applied to all "singular" elements that meet them. — Neil Lunn, Jun 10 '17 at 09:41
Your question does not show an `$elemMatch` condition so this can lead to incorrect results and needs to be corrected. But you have another problem in that you also have nested arrays. The [positional `$` operator](https://docs.mongodb.com/manual/reference/operator/projection/positional/) can only match at the **"first"** and "outer" array position, which makes projecting any inner array impossible. So after fixing the initial query, you need an aggregation operation with [`$filter`](https://docs.mongodb.com/manual/reference/operator/aggregation/filter/) or similar in order to match that. — Neil Lunn, Jun 10 '17 at 10:11
See also [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) for more detail and examples. You have a bit of research to do here to come to understanding how this works. — Neil Lunn, Jun 10 '17 at 10:12
Also please do not make statements like *"I try this with no success..."* when such an attempt is not actually in your question. if you "tried it" then include the code with an [Edit](https://stackoverflow.com/posts/44471800/edit). Then we can all see what you **really** tried, and then tell you if you are right or if you are wrong. — Neil Lunn, Jun 10 '17 at 10:14

Neil Lunn · Accepted Answer · 2017-06-10T23:06:58.903

I'm just going to hardcode some values here to match the "first" array index of "polaire" and the "second" array index of "matrice" for demonstration. Note here the usage of $elemMatch in the $match aggregation pipeline stage and the usage of $map and $filter in the $project pipeline stage:

Aggregation aggregation = newAggregation(
  match(
    Criteria.where("name").is("race").and("polaire").elemMatch(
      Criteria.where("voile").is("foc")
        .and("matrice").elemMatch(
          Criteria.where("vitRange.min").lt(5)
            .and("vitRange.max").gt(5)
            .and("twaRange.min").lt(32)
            .and("twaRange.max").gt(32)
        )
    )
  ),
  project("name")
    .and(new AggregationExpression() {
      @Override
      public DBObject toDbObject(AggregationOperationContext context) {
        return new BasicDBObject("$map",
          new BasicDBObject("input",new BasicDBObject(
            "$filter", new BasicDBObject(
              "input", "$polaire")
              .append("as","p")
              .append("cond", new BasicDBObject("$eq", Arrays.asList("$$p.voile","foc")))
          ))
          .append("as","p")
          .append("in", new BasicDBObject(
            "voile", "$$p.voile")
            .append("matrice",new BasicDBObject(
              "$filter", new BasicDBObject(
                "input", "$$p.matrice")
                .append("as","m")
                .append("cond", new BasicDBObject(
                  "$and", Arrays.asList(
                    new BasicDBObject("$lt", Arrays.asList("$$m.vitRange.min", 5)),
                    new BasicDBObject("$gt", Arrays.asList("$$m.vitRange.max", 5)),
                    new BasicDBObject("$lt", Arrays.asList("$$m.twaRange.min", 32)),
                    new BasicDBObject("$gt", Arrays.asList("$$m.twaRange.max", 32))
                  )
                ))
            ))
          )
        );
      }
    }).as("polaire")
);

Which translates to this serialization:

[
  { "$match": {
    "name": "race",
    "polaire": {
      "$elemMatch": {
        "voile": "foc",
        "matrice": {
          "$elemMatch": {
            "vitRange.min": { "$lt": 5 },
            "vitRange.max": { "$gt": 5 },
            "twaRange.min": { "$lt": 32 },
            "twaRange.max": { "$gt": 32 }
          }
        }
      }
    }
  }},
  { "$project": {
    "name": 1,
    "polaire": {
       "$map": {
         "input": {
           "$filter": {
             "input": "$polaire",
             "as": "p",
             "cond": { "$eq": [ "$$p.voile", "foc" ] }
           } 
         },
         "as": "p",
         "in": {
           "voile": "$$p.voile",
           "matrice": {
             "$filter": {
               "input": "$$p.matrice",
               "as": "m",
               "cond": {
                 "$and": [
                   { "$lt": [ "$$m.vitRange.min", 5 ] },
                   { "$gt": [ "$$m.vitRange.max", 5 ] },
                   { "$lt": [ "$$m.twaRange.min", 32 ] },
                   { "$gt": [ "$$m.twaRange.max", 32 ] }
                 ]
               }
             }
           }
         }
       }
     }
  }}
]

And produces the matched document output as:

{
    "_id" : ObjectId("593bc2f15924d4206cc6e399"),
    "name" : "race",
    "polaire" : [
        {
            "voile" : "foc",
            "matrice" : [
                    {
                            "vitRange" : {
                                    "min" : 4,
                                    "max" : 6
                            },
                            "twaRange" : {
                                    "min" : 30,
                                    "max" : 33
                            },
                            "values" : [
                                    0,
                                    0,
                                    2.4,
                                    3.7
                            ]
                    }
            ]
        }
    ]
}

The "query" portion of $match is important to actually select the "document(s)" that meet the conditions. Without the usage of $elemMatch the expression can actually match documents without the correct conditions on the same inner elements and in fact would be spread across all array elements present in the document(s).

Filtering the array which is nested first uses $map since the "inner" array element is also going to be subject to its own "filtering". So both the "input" source for the $map as well as the "output" as "in" make reference to $filter conditions in order to match the specific element(s) of the arrays.

As the "conditions" ( "cond" ) to $filter we make use of "logical aggregation expressions" such as the boolean $and as well as the other "comparison operators" to mimic the same conditions of their "query operator" counterparts. These are responsible for the logic that matches the correct array items to return in the "filtered" result.

For reference this is the source data from which the results are obtained which should be the same as posted in the question:

{
        "_id" : ObjectId("593bc2f15924d4206cc6e399"),
        "name" : "race",
        "polaire" : [
                {
                        "voile" : "foc",
                        "matrice" : [
                                {
                                        "vitRange" : {
                                                "min" : 0,
                                                "max" : 4
                                        },
                                        "twaRange" : {
                                                "min" : 0,
                                                "max" : 30
                                        },
                                        "values" : [
                                                0,
                                                0,
                                                0,
                                                2.4
                                        ]
                                },
                                {
                                        "vitRange" : {
                                                "min" : 4,
                                                "max" : 6
                                        },
                                        "twaRange" : {
                                                "min" : 30,
                                                "max" : 33
                                        },
                                        "values" : [
                                                0,
                                                0,
                                                2.4,
                                                3.7
                                        ]
                                }
                        ]
                },
                {
                        "voile" : "spi",
                        "matrice" : [
                                {
                                        "vitRange" : {
                                                "min" : 0,
                                                "max" : 4
                                        },
                                        "twaRange" : {
                                                "min" : 0,
                                                "max" : 30
                                        },
                                        "values" : [
                                                0,
                                                0,
                                                0,
                                                1.4
                                        ]
                                },
                                {
                                        "vitRange" : {
                                                "min" : 4,
                                                "max" : 6
                                        },
                                        "twaRange" : {
                                                "min" : 30,
                                                "max" : 33
                                        },
                                        "values" : [
                                                0,
                                                0,
                                                1.4,
                                                2.2
                                        ]
                                }
                        ]
                }
        ]
}

So many thanks for your explanation... I paste your bit of code and just adding List dbObjects = mongoTemplate.aggregate(aggregation, "collectionname", Polaires.class).getMappedResults() to get result But dbObjects is empty — anthony44, Jun 10 '17 at 13:08
@anthony44 Everything here is cut and paste from a running operation on the data you supplied in your question. Check through it carefully again and make sure you are running against the correct collection with matching data. I notice you are typing `"collectionname"` which is likely not your actual collection name, so you probably need to correct that. Also the serialized output can be pasted directly into a mongo shell and run against the correct collection. All output works for me as is shown. — Neil Lunn, Jun 10 '17 at 13:17
I replace collectionname and all work perfectly... Next step I had to replace the hard code values. Thanks a lot Neil. — anthony44, Jun 10 '17 at 13:24
@anthony44 Good. You're new here, so don't forget to [accept your answers](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) and vote too :) That's how we say "thanks!" here. — Neil Lunn, Jun 10 '17 at 13:26
Oups, i need to add filter on name item ("race")... try to modify match clause with : match(Criteria.where("name").is(name).and("sails").elemMatch(...). Could you help me to modify AggregationExpression ? — anthony44, Jun 10 '17 at 18:16
@anthony44 I added the "name", so now all the criteria you were looking for in the initial question have been addressed — Neil Lunn, Jun 10 '17 at 23:09
I am getting error as `[The 'cursor' option is required, except for aggregate with the explain argument]` — Muddassir Rahman, Oct 06 '21 at 11:22

Spring data Match and Filter Nested Array

1 Answers1

Linked

Related