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_
– prasad_, Commented Mar 16, 2020 at 8:19
Yes. What are other methods to achieve this since $ operator cannot be used more than once? — BooleanUniverse
– BooleanUniverse, Commented Mar 16, 2020 at 10:46
Here is a post with similar issue (adding an array element in a nested array): stackoverflow.com/questions/60035042/… — prasad_
– prasad_, Commented Mar 16, 2020 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. — BooleanUniverse
– BooleanUniverse, Commented Mar 18, 2020 at 4:03
There is a difference between $ positional and $[<id>] filtered positional operator . 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_
– prasad_, Commented Mar 18, 2020 at 5:07

prasad_ · Accepted Answer · 2020-04-01 02:04:47Z

1

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:

answered Apr 1, 2020 at 2:04

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BooleanUniverse Over a year ago

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.

prasad_ Over a year ago

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_ Over a year ago

What version of Spring Data are you using?

BooleanUniverse Over a year ago

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.

BooleanUniverse · Accepted Answer · 2020-04-01 10:21:38Z

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);

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related