cbl 2.0
How do I query docs when looking for properties of objects that are within objects themselves?
What ways are there to update those values and to add new properties to a nested object?
I’ve tried playing around with the code in Swift since this blog post but I seem to only be able to go 2 objects deep
How do I treat objects containing arrays of objects differently than objects containing multiple objects?
Let’s consider this example -
{
"type":"university",
"students":[
{
"id":"student_112"
},
{
"id":"student_189"
},
{
"id":"student_1209"
}
]
}
Let’s suppose you are looking to query for documents based on the “id” property of “students” array
let searchQuery = Query
.select(SelectResult.expression(Expression.meta().id))
.from(DataSource.database(db))
.where(Expression.property("type").equalTo("university")
.and(Expression.any("STUDENT").in(Expression.property("students"))
.satisfies(Expression.variable("STUDENT.id").equalTo("student_112"))))
.limit(limit)
The next part of blog post would be discussing collections and will cover the above example. There are some enhancements to the API . So stay tuned.
Can I provide an example that is giving me trouble, for this case all documents where type = “nflgame” would be similar to:
{
"type": "nflgame"
"pools": {
"weekly": {
"picks": {
"week1": [
{
"picksheet": [
{
"gameid": "2",
"pick": "bills"
},
{
"gameid": "3",
"pick": "falcons"
},
{
"gameid": "4",
"pick": "bengals"
},
{
"gameid": "5",
"pick": "steelers"
},
{
"gameid": "6",
"pick": "lions"
},
{
"gameid": "7",
"pick": "texans"
},
{
"gameid": "9",
"pick": "titans"
},
{
"gameid": "10",
"pick": "eagles"
},
{
"gameid": "11",
"pick": "rams"
},
{
"gameid": "12",
"pick": "seahawks"
},
{
"gameid": "13",
"pick": "panthers"
},
{
"gameid": "14",
"pick": "cowboys"
},
{
"gameid": "15",
"pick": "vikings"
},
{
"gameid": "16",
"pick": "chargers"
}
],
"tieBreaker": 64
},
{
"picksheet": [
{
"gameid": "2",
"pick": "bills"
},
{
"gameid": "3",
"pick": "falcons"
},
{
"gameid": "4",
"pick": "bengals"
},
{
"gameid": "5",
"pick": "steelers"
},
{
"gameid": "6",
"pick": "lions"
},
{
"gameid": "7",
"pick": "jaguars"
},
{
"gameid": "9",
"pick": "raiders"
},
{
"gameid": "10",
"pick": "eagles"
},
{
"gameid": "11",
"pick": "rams"
},
{
"gameid": "12",
"pick": "packers"
},
{
"gameid": "13",
"pick": "panthers"
},
{
"gameid": "14",
"pick": "cowboys"
},
{
"gameid": "15",
"pick": "vikings"
},
{
"gameid": "16",
"pick": "chargers"
}
],
"sheet": "extra",
"tieBreaker": 64
},
{
"picksheet": [
{
"gameid": "2",
"pick": "bills"
},
{
"gameid": "3",
"pick": "falcons"
},
{
"gameid": "4",
"pick": "bengals"
},
{
"gameid": "5",
"pick": "steelers"
},
{
"gameid": "6",
"pick": "lions"
},
{
"gameid": "7",
"pick": "jaguars"
},
{
"gameid": "9",
"pick": "raiders"
},
{
"gameid": "10",
"pick": "eagles"
},
{
"gameid": "11",
"pick": "rams"
},
{
"gameid": "12",
"pick": "packers"
},
{
"gameid": "13",
"pick": "panthers"
},
{
"gameid": "14",
"pick": "cowboys"
},
{
"gameid": "15",
"pick": "vikings"
},
{
"gameid": "16",
"pick": "chargers"
}
],
"sheet": "extra",
"tieBreaker": 64
}
],
"week2": [
{
"picksheet": [
{
"gameid": "2",
"pick": "panthers"
},
{
"gameid": "3",
"pick": "cardinals"
},
{
"gameid": "4",
"pick": "titans"
},
{
"gameid": "5",
"pick": "chiefs"
},
{
"gameid": "6",
"pick": "patriots"
},
{
"gameid": "7",
"pick": "steelers"
},
{
"gameid": "8",
"pick": "ravens"
},
{
"gameid": "9",
"pick": "buccaneers"
},
{
"gameid": "10",
"pick": "raiders"
},
{
"gameid": "11",
"pick": "chargers"
},
{
"gameid": "12",
"pick": "cowboys"
},
{
"gameid": "13",
"pick": "rams"
},
{
"gameid": "14",
"pick": "seahawks"
},
{
"gameid": "15",
"pick": "packers"
},
{
"gameid": "16",
"pick": "lions"
}
],
"tieBreaker": 48
}
]
},
"poolOwner": "bflex",
"year": "2017"
},
}
}Specifically I wish to pull out the picks/gameid for week1 for each picksheet[]
The following is N1QL query, You can transform the logic accordingly that works in Mobile/Couchbase Lite.
SELECT ARRAY_FLATTEN(d.pools.weekly.picks.week1[*].picksheet,2) AS week1
FROM default AS d WHERE d.type = "nflgame";
OR
SELECT ARRAY_FLATTEN(ARRAY ARRAY_FLATTEN(v.val[*].picksheet,1) FOR v IN OBJECT_PAIRS(d.pools.weekly.picks) WHEN v.name = "week1" END,1) week1
FROM default AS d WHERE d.type = "nflgame";Well, in CBM 2.0, I don’t believe you can flatten the nested array.
So for now, you can query for pools.weekly.picks.week1 in your SelectResult and you will get the array of (picksheet, tiebreaker) objects that you’d have to filter and flatten.
If your language supports it (e.g… swift), you can use map , flatmap functions on the resulting array to get to the desired results- Should be quite straightforward.
CC @jens
Question: why do you have one large document containing all of the data? This seems to defeat the purpose of having a database. If there’s only one JSON object, you can just write a few lines of native code to pull out the properties you need.
This example is related to a test project we have used to learn more about couchbase, we now recognize that utilizing more docs would be a big improvement, which we will do when we start a new project. This is just a relevant snippet of the original JSON doc which is for an individual user/case and there are multiple users/docs.
Is it possible to select the complete JSON Object
{
“id”:“student_112”
}
from students Array from document you specified?
No, we don’t support the type of query that would let you do that inside the query.
But you can return the array from the query and then use Swift code to find the item you want.
Alright Thank you for your answer