Combining multiple arrays for querying with MongoDB Java driver

Question

I have the following JSON:

{"pid":"b00l16vp","categories":{"category1":["factual", "arts culture and the media", "history"]}}
{"pid":"b0079mpp","categories":{"category2":["childrens", "entertainment and comedy", "animation"],"category1":["signed"]}}
{“pid":"b00htbn3"}
{“pid":"b00gdhqw","categories":{"category2":["factual"],"category3":["scotland"],"category4":["lifestyle and leisure", "food and drink"],"category1":["entertainment", "games and quizzes"]}}

My intention is to query the categories object using an array of String by combining all of the arrays in a single array. I have the following code:

String [] cats = ["childrens", "signed"]
BasicDBObject theProjections = new BasicDBObject()
for (int i = 1; i <= 5; i++) {
    String identifier = "categories.category" + i
    String cleanIdentifier = "\$" + identifier
    //If the category does not exist, put in a blank category
    def temp = [cleanIdentifier, []]
    theMegaArray.add(new BasicDBObject('$ifNull', temp))
}
//The megaArray is the array created in the above loop which combines all arrays
BasicDBObject theData = new BasicDBObject('$setUnion', theMegaArray)
BasicDBObject theFilter = new BasicDBObject('input', theData)
theFilter.put("as", "megaArray")
//all of the values found in cats should match the megaArray
theFilter.put("cond", new BasicDBObject('$all', ["\$\$megaArray", cats]))
theProjections.put('$filter', theFilter)
FindIterable iterable = collection.find(criteria).projection(theProjections)

I have used this question to come to write this code so far. The $setUnion expects all of the fields to appear however in my JSON, there are a varied number of categrories arrays hence I have used the $ifNull to populate empty categories with []. The $filter has been used to query the cats array on the megaArray.

When running this I'm getting the following error:

Caused by: com.mongodb.MongoQueryException: Query failed with error code 2 and error message '>1 field in obj: { input: { $setUnion: [ { $ifNull: [ "$categories.category1", [] ] }, { $ifNull: [ "$categories.category2", [] ] }, { $ifNull: [ "$categories.category3", [] ] }, { $ifNull: [ "$categories.category4", [] ] }, { $ifNull: [ "$categories.category5", [] ] } ] }, as: "megaArray", cond: { $all: [ "$$megaArray", [ "factual" ] ] } }'

I'm not entirely sure what that means as the looks right. I should also note that the category object does not always exist but I'm not sure if this matters.

Answer 1

You can use the below aggregation pipeline.

Shell Query for reference:

db.collection.aggregate([{
    "$project": {
        "pid": 1,
        "categories": 1,
        "filter": {
            "$eq": [{
                    "$setUnion": [{
                        "$ifNull": ["$categories.category1", []]
                    }, {
                        "$ifNull": ["$categories.category2", []]
                    }, {
                        "$ifNull": ["$categories.category3", []]
                    }, {
                        "$ifNull": ["$categories.category4", []]
                    }, {
                        "$ifNull": ["$categories.category5", []]
                    }]
                },
                ["childrens", "signed"]
            ]
        }
    }
}, {
    "$match": {
        "filter": true
    }
}])

Java Code:

String[] cats = {"childrens", "signed"};

// Combining the optional categories arrays
 BasicDBList theMegaArray = new BasicDBList();
 for (int i = 1; i <= 5; i++) {
      String identifier = "categories.category" + i;
      String cleanIdentifier = "$" + identifier;
      theMegaArray.add(new BasicDBObject("$ifNull", Arrays.asList(cleanIdentifier, Collections.EMPTY_LIST)));
 }
 BasicDBObject theData = new BasicDBObject("$setUnion", theMegaArray);

// Add equals filter - Compare the arrays and output boolean filter field
 BasicDBObject theFilter = new BasicDBObject("$eq", Arrays.asList(theData, cats));

// Add projections to keep the output fields
 BasicDBObject theProjections = new BasicDBObject();
 theProjections.put("filter", theFilter);
 theProjections.put("pid", 1);
 theProjections.put("categories", 1);

// Add $project stage
 BasicDBObject theProject = new BasicDBObject("$project", theProjections);

// Add $match stage to compare the boolean filter field to true to keep matching documents
 BasicDBObject theMatch = new BasicDBObject("$match", new BasicDBObject("filter", true));

// Add stages to piepline
BasicDBList pipeline = new BasicDBList();
pipeline.add(theProject);
pipeline.add(theMatch);

// Run aggregation
AggregateIterable iterable = collection.aggregate(pipeline);

Answer 2

You can achieve the same result using the aggregation framework

To do this you can first project the data to create the 'megaArray' and then match against the new array.

String [] cats = new String[] {"childrens", "signed"};

List<DBObject> theMegaArray = new ArrayList<>();

BasicDBObject theProjections = new BasicDBObject();

for (int i = 1; i <= 5; i++) {
  String identifier = "categories.category" + i;
  String cleanIdentifier = "$" + identifier;
  //If the category does not exist, put in a blank category
  Object[] temp = new Object[] {cleanIdentifier, new Object[]{}};
  theMegaArray.add(new BasicDBObject("$ifNull", temp));
}

theProjections.put("_id", 1);
theProjections.put("pid", 1);
theProjections.put("categories",1);
theProjections.put("allCategories", new BasicDBObject("$setUnion", theMegaArray));

BasicDBObject theFilter = new BasicDBObject("allCategories", new BasicDBObject("$all", cats));

List<BasicDBObject> pipeline = new ArrayList<>();
pipeline.add(new BasicDBObject("$project", theProjections));
pipeline.add(new BasicDBObject("$match", theFilter));

AggregateIterable iterable = collection.aggregate(pipeline);

The code sample above adds a new array called "allCategories" in the project stage and then matches against this new document.

Another projection stage could be added to remove the allCategories array from the final output