Update a Specific Value in a Multi-Level Nested JSON Document Using N1QL in Couchbase
In this article, see how to update a specific value in a multi-level nested JSON document using N1QL in Couchbase.
Join the DZone community and get the full member experience.
Join For FreeIn the example below, we will explore how to update an object in multi-level JSON data in Couchbase.
Recently, I’ve been working on Couchbase queries and got stuck while attempting to work with array of objects in highly nested json. Then I explored some Array features in Couchbase, which helped me to achieve the result I was looking for. We will go through same with example below.
Start With an Idea
Let’s consider you are running a university and you need data until the student level in all colleges is listed under the university. The document representing the sample data is below.
universityMaster:
xxxxxxxxxx
{
"_class": "com.university.entity.universityDocument",
"university": [
{
"collegeList": [
{
"collegeId": "1",
"collegeName": "Common college",
"collegeType": "Engineering",
"branch": [
{
"studentList": [
{
"studentId": "1",
"studentName": "Alice",
"studentSubjects": [],
"studentType": "TopTalent"
},
{
"studentId": "2",
"studentName": "Alex",
"studentSubjects": [],
"studentType": "Average"
}
],
"branchId": "1",
"branchName": "Computer Science",
"branchStrength": "60"
},
{
"studentList": [
{
"studentId": "3",
"studentName": "Mohit",
"studentSubjects": [],
"studentType": "TopTalent"
},
{
"studentId": "4",
"studentName": "Rajni",
"studentSubjects": [],
"studentType": "Average"
}
],
"branchId": "2",
"branchName": "Electronics",
"branchStrength": "60"
}
]
}
],
"universityName": "BPUT",
"universityId": "1",
"universityType": "Technology",
"universityGrade": "AAA"
}
]
}
Requirement
You need to update student object inside studentList, which is nested under branch, which is under collegeList, which is under university.
Implementation
I need to update a single object that is at level 4 in a nested JSON document. So how can I get it Couchbase Array Functions is the solution.
First, I need to find out the array position of the element that needs to be updated. Let's understand what Array_Position is.
ARRAY_POSITION (expr,val) – This function returns the first position of the specified value within the array expression.
Below is the query:
xxxxxxxxxx
select array_pos(branch.studentList, studentList) from `university`
as v unnest v.university as university
unnest university.collegeList as collegeList
unnest collegeList.branch as branch
unnest branch.studentList as studentList
where university.universityId = "1"
and collegeList.collegeId = "1"
and branch.branchId = "1"
and studentList.studentId = "1"
and meta(v).id = "universityMaster" limit 1
Once the query is executed, it gives the below result:
xxxxxxxxxx
[
{
"$1": 0
}
]
Now I need to update the required value on that position. The query for same is below. As you can see, I have passed the value I got from the above query, i.e “0” in branch.studentList[0]. We need to use Array_Flatten to achieve the same.
ARRAY_FLATTEN (expr,depth) – This function flattens the nested array elements into top level array, up to a specified depth.
xxxxxxxxxx
update `university` v use keys "universityMaster" set branch.studentList[0] = {
"studentId": "1",
"studentName": "Alice D",
"studentSubjects": [],
"studentType": "TopTalent"
}
FOR branch IN ARRAY_FLATTEN( ARRAY collegeList.branch
FOR collegeList IN ARRAY_FLATTEN( ARRAY university.collegeList
FOR university WITHIN v.university WHEN university.universityId = "1" END, 1)
WHEN collegeList.collegeId = "1" END, 1)
WHEN branch.branchId = "1"
End returning *;
Results are below:
[
{
"v": {
"_class": "com.university.entity.universityDocument",
"university": [
{
"collegeList": [
{
"branch": [
{
"branchId": "1",
"branchName": "Computer Science",
"branchStrength": "60",
"studentList": [
{
"studentId": "1",
"studentName": "Alice D",
"studentSubjects": [],
"studentType": "TopTalent"
},
{
"studentId": "2",
"studentName": "Alex",
"studentSubjects": [],
"studentType": "Average"
}
]
},
{
"branchId": "2",
"branchName": "Electronics",
"branchStrength": "60",
"studentList": [
{
"studentId": "3",
"studentName": "Mohit",
"studentSubjects": [],
"studentType": "TopTalent"
},
{
"studentId": "4",
"studentName": "Rajni",
"studentSubjects": [],
"studentType": "Average"
}
]
}
],
"collegeId": "1",
"collegeName": "Common college",
"collegeType": "Engineering"
}
],
"universityGrade": "AAA",
"universityId": "1",
"universityName": "BPUT",
"universityType": "Technology"
}
]
}
}
]
Finally, I got the solution.
When Should I Use Nested Objects?
Nested objects are the solution when I need data structures containing collections of inner objects that are tightly coupled with outer objects or describes the outer object. So I have designed the above-mentioned example data set, which clearly describes the logic, i.e inside university many colleges, inside colleges many branches, and in each branch, many students are tagged.
Opinions expressed by DZone contributors are their own.
Comments