Composite Query enables you to mix statistics from existing queries after which follow filters, aggregates, and so on earlier than imparting the record consequences, which show the mixed records set. Composite Query retrieves multiple stages of associated information on present queries and affords the mixed facts as a single and flattened query result.
Using Composite Query, you furthermore may have the option to −
- Select the SQL pruning option to take away tables and fields that are not wished primarily based on users’ characteristic alternatives.
- Set the ORDER BY and GROUP BY clauses.
- Set the WHERE clause as a filter over the result set of a composite query.
The above operators may be composed to form extra effective queries. Since DocumentDB supports nested collections, the composition can either be concatenated or nested.
Let’s don't forget the following documents for this situation.
AndersenFamily file is as follows.
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas", "relationship": "father" },
{ "firstName": "Mary Kay", "relationship": "mother" }
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ]
}
],
"location": { "state": "WA", "county": "King", "city": "Seattle" },
"isRegistered": true
}
SmithFamily file is as follows.
{
"id": "SmithFamily",
"parents": [
{ "familyName": "Smith", "givenName": "James" },
{ "familyName": "Curtis", "givenName": "Helen" }
],
"children": [
{
"givenName": "Michelle",
"gender": "female",
"grade": 1
},
{
"givenName": "John",
"gender": "male",
"grade": 7,
"pets": [
{ "givenName": "Tweetie", "type": "Bird" }
]
}
],
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
},
"isRegistered": true
}
WakefieldFamily report is as follows.
{
"id": "WakefieldFamily",
"parents": [
{ "familyName": "Wakefield", "givenName": "Robin" },
{ "familyName": "Miller", "givenName": "Ben" }
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{ "givenName": "Charlie Brown", "type": "Dog" },
{ "givenName": "Tiger", "type": "Cat" },
{ "givenName": "Princess", "type": "Cat" }
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{ "givenName": "Jake", "type": "Snake" }
]
}
],
"location": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
}
Let’s check an instance of concatenated question.
Following is the query with the intention to retrieve the identity and vicinity of the own family in which the first child givenName is Michelle.
SELECT f.id,f.location
FROM Families f
WHERE f.children[0].givenName = "Michelle"
When the above query is done, it produces the following output.
[
{
"id": "SmithFamily",
"location": {
"state": "NY",
"county": "Queens",
"city": "Forest Hills"
}
}
]
Let’s take into account another example of concatenated question.
Following is the question that allows you to return all of the documents in which the primary toddler grade greater than three.
SELECT *
FROM Families f
WHERE ({grade: f.children[0].grade}.grade > 3)
When the above query is carried out, it produces the following output.
[
{
"id": "WakefieldFamily",
"parents": [
{
"familyName": "Wakefield",
"givenName": "Robin"
},
{
"familyName": "Miller",
"givenName": "Ben"
}
],
"children": [
{
"familyName": "Merriam",
"givenName": "Jesse",
"gender": "female",
"grade": 6,
"pets": [
{
"givenName": "Charlie Brown",
"type": "Dog"
},
{
"givenName": "Tiger",
"type": "Cat"
},
{
"givenName": "Princess",
"type": "Cat"
}
]
},
{
"familyName": "Miller",
"givenName": "Lisa",
"gender": "female",
"grade": 3,
"pets": [
{
"givenName": "Jake",
"type": "Snake"
}
]
}
],
"location": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
"isRegistered": false,
"_rid": "Ic8LAJFujgECAAAAAAAAAA==",
"_ts": 1450541623,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
"_etag": "\"00000500-0000-0000-0000-567582370000\"",
"_attachments": "attachments/"
},
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{
"firstName": "Thomas",
"relationship": "father"
},
{
"firstName": "Mary Kay",
"relationship": "mother"
}
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [
{
"givenName": "Fluffy",
"type": "Rabbit"
}
]
}
],
"location": {
"state": "WA",
"county": "King",
"city": "Seattle"
},
"isRegistered": true,
"_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
"_ts": 1450541624,
"_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
"_etag": "\"00000700-0000-0000-0000-567582380000\"",
"_attachments": "attachments/"
}
]
Let’s check an example of nested queries.
Following is the query as a way to iterate all of the mother and father after which go back the document where familyName is Smith.
SELECT *
FROM p IN Families.parents
WHERE p.familyName = "Smith"
When the above query is done, it produces the following output.
[
{
"familyName": "Smith",
"givenName": "James"
}
]
Let’s consider every other example of nested query.
Following is the query in order to go back all the familyName.
SELECT VALUE p.familyName
FROM Families f
JOIN p IN f.parents
When the above query is accomplished, it produces he following output.
[
"Wakefield",
"Miller",
"Smith",
"Curtis"
]