How to add a json in a nested array of a mongodb document using Spring?

Question

Document stored in mongodb:


{
"CNF_SERVICE_ID":"1",
"SERVICE_CATEGORY":"COMMON_SERVICE",
"SERVICES":[{
    "SERVICE_NAME":"Authentication Service",
    "VERSIONS":[{
            "VERSION_NAME":"AuthenticationServiceV6_3",
            "VERSION_NUMBER":"2",
            "VERSION_NOTES":"test",
            "RELEASE_DATE":"21-02-2020",
            "OBSOLETE_DATE":"21-02-2020",
            "STATUS":"Y",
            "GROUPS":[{
                "GROUP_NAME":"TEST GROUP",
                "CREATED_DATE":"",
                "NODE_NAMES":[
                    ""
                    ],
                "CUSTOMERS":[{
                    "CUSTOMER_CONFIG_ID":"4",
                    "ACTIVATION_DATE":"21-02-2020",
                    "DEACTIVATION_DATE":"21-02-2020",
                    "STATUS":"Y"
                }]
            }]
        }]
    }
    ]
}

Now, I need to add another customer json to the array "CUSTOMERS" inside "GROUPS" in the same document above. The customer json would be like this:

{
    "CUSTOMER_CONFIG_ID":"10",
    "ACTIVATION_DATE":"16-03-2020",
    "DEACTIVATION_DATE":"16-03-2021",
    "STATUS":"Y"
}

I tried this:


Update update = new Update().push("SERVICES.$.VERSIONS.GROUPS.CUSTOMERS",customerdto);
mongoOperations.update(query, update, Myclass.class, "mycollection");

But, I am getting the exception: org.springframework.data.mongodb.UncategorizedMongoDbException: Command failed with error 28 (PathNotViable): 'Cannot create field 'GROUPS' in element

[ EDIT ADD ]

I was able to update it using the filtered positional operator. Below is the query I used:

 update( 
   { "SERVICE_CATEGORY":"COMMON_SERVICE", "SERVICES.SERVICE_NAME":"Authentication Service", "SERVICES.VERSIONS.VERSION_NAME":"AuthenticationServiceV6_3"}, 
   { $push:{"SERVICES.$[].VERSIONS.$[].GROUPS.$[].CUSTOMERS": { "CUSTOMER_CONFIG_ID":"6", "ACTIVATION_DATE":"31-03-2020", "STATUS":"Y" } } } 
 );

Actually, this query updated all the fields irrespective of the filter conditions. So. I tried this but I am facing syntax exception. Please help.

update(
 {"SERVICE_CATEGORY":"COMMON_SERVICE"},
 {"SERVICES.SERVICE_NAME":"Authentication Service"},
 {"SERVICES.VERSIONS.VERSION_NAME":"AuthenticationServiceV6_3"}
 {
    $push:{"SERVICES.$[service].VERSIONS.$[version].GROUPS.$[group].CUSTOMERS":{
        "CUSTOMER_CONFIG_ID":"6",
        "ACTIVATION_DATE":"31-03-2020",
        "STATUS":"Y"
    }
    }
 },
 {
        multi: true,
        arrayFilters: [ { $and:[{ "version.VERSION_NAME": "AuthenticationServiceV6_3"},{"service.SERVICE_NAME":"Authentication Service"},{"group.GROUP_NAME":"TEST GROUP"}]} ]
    }
 );

Update: April 1,2020

The code I tried:

validationquery.addCriteria(Criteria.where("SERVICE_CATEGORY").is(servicedto.getService_category()).and("SERVICES.SERVICE_NAME").is(servicedetail.getService_name()).and("SERVICES.VERSIONS.VERSION_NAME").is(version.getVersion_name()));
Update update=new Update().push("SERVICES.$[s].VERSIONS.$[v].GROUPS.$[].CUSTOMERS", customer).filterArray(Criteria.where("SERVICE_CATEGORY").is(servicedto.getService_category()).and("s.SERVICE_NAME").is(servicedetail.getService_name()).and("v.VERSION_NAME").is(version.getVersion_name()));
mongoOperations.updateMulti(validationquery, update, ServiceRegistrationDTO.class, collection, key,env);

The below exception is thrown:

ERROR com.sample.amt.mongoTemplate.MongoOperations - Exception in count(query, collectionName,key,env) :: org.springframework.dao.DataIntegrityViolationException: Error parsing array filter :: caused by :: Expected a single top-level field name, found 'SERVICE_CATEGORY' and 's'; nested exception is com.mongodb.MongoWriteException: Error parsing array filter :: caused by :: Expected a single top-level field name, found 'SERVICE_CATEGORY' and 's'

You have to use `$arrayFilters`. The positional `$` operator cannot be used for queries which traverse more than one array (you have nested arrays). Also, post the query for your update operation. — prasad_, Mar 16 '20 at 08:19
Yes. What are other methods to achieve this since $ operator cannot be used more than once? — Sangames Kumar, Mar 16 '20 at 10:46
Here is a post with similar issue (adding an array element in a nested array): https://stackoverflow.com/questions/60035042/mongodb-document-update-array-element-using-findoneandupdate-method-in-java — prasad_, Mar 16 '20 at 11:36
@prasad_ The post had only 2 levels of nesting. i.e, Array inside an array. So, positional operator did the operation. But, in my case there are 4 levels of nesting. Please suggest what I can do to add an element to the 'CUSTOMER' arraylist. — Sangames Kumar, Mar 18 '20 at 04:03
There is a difference between [$ positional](https://docs.mongodb.com/manual/reference/operator/update/positional/) _and_ `$[]` [filtered positional operator](https://docs.mongodb.com/manual/reference/operator/update/positional-filtered/) . I think you have to try to use the _filtered positional operator_. Also, I had already mentioned in my first comment that you have to post (please update your question) the _query_ aspect of the update operation. — prasad_, Mar 18 '20 at 05:07
@prasad_ Thanks. I was able to update it using the filtered positional operator. Below is the query I used: db.collectionname.update({ "SERVICE_CATEGORY":"COMMON_SERVICE", "SERVICES.SERVICE_NAME":"Authentication Service", "SERVICES.VERSIONS.VERSION_NAME":"AuthenticationServiceV6_3"}, { $push:{"SERVICES.$[].VERSIONS.$[].GROUPS.$[].CUSTOMERS":{ "CUSTOMER_CONFIG_ID":"6", "ACTIVATION_DATE":"31-03-2020", "STATUS":"Y" } } } ); — Sangames Kumar, Mar 31 '20 at 10:40
@prasad_ I am sorry. But this update query pushed the customer object json under all the GROUPS fields. How to push only for the condition : "SERVICE_CATEGORY":"COMMON_SERVICE", "SERVICES.SERVICE_NAME":"Authentication Service", "SERVICES.VERSIONS.VERSION_NAME":"AuthenticationServiceV6_3"? — Sangames Kumar, Mar 31 '20 at 12:31
I tried further and found the arrayfilter conditions has to be added. So, I tried by like this : SERVICES.$[SERVICE].VERSIONS.$[VERSION].GROUPS.$.CUSTOMERS and adding criteria inside filterArray. But, I am getting the below error now: {"ok": 0.0, "errmsg": "Error parsing array filter :: caused by :: Expected a single top-level field name, found 'SERVICE_CATEGORY' and 'SERVICE'", "code": 9, "codeName": "FailedToParse"} — Sangames Kumar, Mar 31 '20 at 12:54
_"I am sorry. But this update query pushed the customer object json under all the GROUPS fields."_ Then you must use the `arrayFilters` to target only specific nested array. To add to a specific GROUP (and not all of them), you can specify that condition in the `arrayFilters`. For this the update is specified using the _filtered positional operator_ (see my earlier comment and the link to it). — prasad_, Mar 31 '20 at 13:14
_"I tried further and found the arrayfilter conditions has to be added. So, I tried by like this ..."_ Can you edit your post and add the complete update query you tried. From your comment it doesn't look right. (1) You are again using the `$` operator (this is valid with nested arrays of one level only), (2) you cannot use the same name as the field name for the filtered positional operator. Also, the _identifier_ used with the filtered positional operator _must_ start with a lower-case (you are using upper-case). — prasad_, Mar 31 '20 at 13:46
@prasad_ I am a beginner. I actually don't know the exact query as well. I have now posted in the question what I tried. Also, I did not use the $ operator again, I used the $[] operator. I have also not used the same name as field name for filtered positional operator. Now, I changed to lower case. — Sangames Kumar, Mar 31 '20 at 15:26

score 1 · Answer 1 · answered Apr 01 '20 at 02:04

This update query adds the JSON to the nested array, "SERVICES.VERSIONS.GROUPS.CUSTOMERS", based upon the specified filter conditions. Note that your filter conditions direct the update operation to the specific array (of the nested arrays).

// JSON document to be added to the CUSTOMERS array
new_cust = { 
             "CUSTOMER_CONFIG_ID": "6", 
             "ACTIVATION_DATE": "31-03-2020", 
             "STATUS": "Y" 
}

db.collection.update( 
  { 
      "SERVICE_CATEGORY": "COMMON_SERVICE", 
      "SERVICES.SERVICE_NAME": "Authentication Service",
      "SERVICES.VERSIONS.VERSION_NAME": "AuthenticationServiceV6_3"
  }, 
  { 
      $push: { "SERVICES.$[s].VERSIONS.$[v].GROUPS.$[g].CUSTOMERS": new_cust } 
  },
  {
      multi: true,
      arrayFilters: [
          { "s.SERVICE_NAME": "Authentication Service" },
          { "v.VERSION_NAME": "AuthenticationServiceV6_3" },
          { "g.GROUP_NAME": "TEST GROUP" }
      ]
  }
);

Few things to note when updating documents with nested arrays of more than one level nesting.

Use the all positional operator $[] and the filtered positional operator $[<identifier>], and not the $ positional operator. With filtered positional operator specify the array filter conditions using the arrayFilters parameter. Note that this will direct your update to target the specific nested array.
For the filtered positional operator $[<identifier>], the identifier must begin with a lowercase letter and contain only alphanumeric characters.

References:

Thanks for your help! Just another doubt...If the arrayfilter is filtering based on the conditions we provided, why are the first parameters in update function is even needed? Also, please guide me on how to the same query using Spring MongoTemplate method updateMulti. — Sangames Kumar, Apr 01 '20 at 09:01
When you have a number of documents in a collection, the initial filter will only select documents matching the initial conditions in the filter. `arrayFilters` is for targeting specific sub-documents within arrays. — prasad_, Apr 01 '20 at 09:05
Okay. The version of the related dependencies I am using are below: spring-boot-starter-data-mongodb : 2.2.2.RELEASE -> mongodb-driver : 3.11.2 -> spring-data-mongodb : 2.2.3.RELEASE I have also update the question with my latest code. — Sangames Kumar, Apr 01 '20 at 09:39

score 0 · Accepted Answer · answered Apr 01 '20 at 10:21

Thanks to @prasad_ for providing the query. I was able to eventually convert the query successfully to code with Spring data MongoTemplate's updateMulti method. I have posted the code below:

Query validationquery = new Query();
                        validationquery.addCriteria(Criteria.where("SERVICE_CATEGORY").is(servicedto.getService_category()).and("SERVICES.SERVICE_NAME").is(servicedetail.getService_name()).and("SERVICES.VERSIONS.VERSION_NAME").is(version.getVersion_name()));

Update update=new Update().push("SERVICES.$[s].VERSIONS.$[v].GROUPS.$[].CUSTOMERS", customer).filterArray(Criteria.where("s.SERVICE_NAME").is(servicedetail.getService_name())).filterArray(Criteria.where("v.VERSION_NAME").is(version.getVersion_name()));    
                            mongoOperations.updateMulti(validationquery, update, ServiceRegistrationDTO.class, collection, key,env);
mongoTemplateobj.updateMulti(validationquery, update, ServiceRegistrationDTO.class, collection, key,env);

How to add a json in a nested array of a mongodb document using Spring?

2 Answers2

Linked