{
"id": "WakefieldFamily",
"parents": [
{"familyName": "Wakefield", "givenName": "Robin" },
{"familyName": "Miller", "givenName": "Ben"}
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender ": "female",
"grade": 1,
"pets": [
{"givenName": "Goofy" },
{"givenName": "Shadow "}
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 8
}
],
"address": {"state": "NY", "county": "Manhattan", " city": "NY" },
"isRegistered": false
};
The pet’s name for a child is called “fool”, how can I find it?
It seems that the following syntax is invalid
Select * from root r
WHERE r.children.pets.givenName="Goofy" pre>Instead, I need to do it
Select * from root r
WHERE r.children[0].pets[0].givenName="Goofy "This is not really searching through arrays.
Any suggestions on how to handle these queries?
In simple terms, you can treat DocumentDB's JOIN as a self-join, which can be used to form cross products between nested JSON objects. /p>
In the context of asking a child of a pet named "Goofy", you can try:
SELECT
f.id AS familyName,
c AS child,
p.givenName AS petName
FROM Families f
JOIN c IN f.children
JOIN p IN c.pets
WHERE p .givenName = "Goofy"
Which one returns:
[{
familyName: WakefieldFamily,
child: {
familyName: Merriam,
givenName: Jesse,
gender: female,
grade: 1,
pets: [{
givenName: Goofy
}, {
givenName: Shadow
}]
},
petName: Goofy
}]
Reference: http://azure.microsoft. com/en-us/documentation/articles/documentdb-sql-query/
Edit:
You can also use the ARRAY_CONTAINS function, as shown below:
FROM food
WHERE food.id = "09052" or ARRAY_CONTAINS(food.tags.name, "blueberries") < /div>
In such an Azure Documentdb document
{
"id": "WakefieldFamily",
"parents": [
{"familyName": "Wakefield", "givenName": "Robin" },
{"familyName": "Miller", "givenName": "Ben" }< br />],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender" : "female",
"grade": 1,
"pets": [
{"givenName": "Goofy" },
{"givenName": "Shadow" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": " female",
"grade": 8
}
],
"address": {"state": "NY", "county": "Manhattan", "city ": "NY" },
"isRegistered": false
};
The name given to the child by the pet is “fool”, how can I find it?
It seems that the following syntax is invalid
Select * from root r
WHERE r.children.pets.givenName="Goofy" pre>Instead, I need to do it
Select * from root r
WHERE r.children[0].pets[0].givenName="Goofy "This is not really searching through arrays.
Any suggestions on how to handle these queries?
You should use DocumentDB's JOIN clause, which is different from JOIN in RDBM (because DocumentDB handles the denormlaize data model of schemaless documents). < p>
In simple terms, you can regard DocumentDB's JOIN as a self-join, which can be used to form cross products between nested JSON objects.
The query is called "Goofy" In the context of pet children, you can try:
SELECT
f.id AS familyName,
c AS child,
p.givenName AS petName
FROM Families f
JOIN c IN f.children
JOIN p IN c.pets
WHERE p.givenName = "Goofy"Which returns:
[{
familyName: WakefieldFamily,
child: {
familyName: Merriam,
givenName: Jesse ,
gender: female,
grade: 1,
pets: [{
givenName: Goofy
}, {
givenName: Shadow
}]
},
petName: Goofy
}]Reference: http://azure.microsoft.com/en-us/documentation/articles/documentdb- sql-query/
Edit:
You can also use the ARRAY_CONTAINS function, as shown below:
SELECT food.id, food.description, food.tags
FROM food
WHERE food.id = "09052" or ARRAY_CONTAINS(food.tags.name, "blueberries")