Make results in the SQL++ reference doc pretty.
Change-Id: Ifaba037b023e76b90a0520751b2914757ec191cf
Reviewed-on: https://asterix-gerrit.ics.uci.edu/1287
Sonar-Qube: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Till Westmann <tillw@apache.org>
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
index f1ebc47..5ca0e1f 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -106,10 +106,31 @@
This query returns:
- [
- { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }
-
- ]
+ [{
+ "userSince": "2012-08-20T10:10:00.000Z",
+ "friendIds": [
+ 2,
+ 3,
+ 6,
+ 10
+ ],
+ "gender": "F",
+ "name": "MargaritaStoddard",
+ "nickname": "Mags",
+ "alias": "Margarita",
+ "id": 1,
+ "employment": [
+ {
+ "organizationName": "Codetechno",
+ "start-date": "2006-08-06"
+ },
+ {
+ "end-date": "2010-01-26",
+ "organizationName": "geomedia",
+ "start-date": "2010-06-17"
+ }
+ ]
+ } ]
### <a id="SQL_select">SQL-style SELECT</a>
In SQL++, the traditional SQL-style `SELECT` syntax is also supported.
@@ -123,9 +144,10 @@
Returns:
- [
- {"user_alias":"Margarita","user_name":"MargaritaStoddard"}
- ]
+ [ {
+ "user_name": "MargaritaStoddard",
+ "user_alias": "Margarita"
+ } ]
### <a id="Select_star">SELECT *</a>
In SQL++, `SELECT *` returns a record with a nested field for each input tuple. Each field has as its field name the name of a binding variable generated by either the `FROM` clause or `GROUP BY` clause in the current enclosing `SELECT` statement, and its field is the value of that binding variable.
@@ -137,11 +159,71 @@
Since `user` is the only binding variable generated in the `FROM` clause, this query returns:
- [
- { "user": { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" } },
- { "user": { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" } },
- { "user": { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] } }
- ]
+ [ {
+ "user": {
+ "userSince": "2012-08-20T10:10:00.000Z",
+ "friendIds": [
+ 2,
+ 3,
+ 6,
+ 10
+ ],
+ "gender": "F",
+ "name": "MargaritaStoddard",
+ "nickname": "Mags",
+ "alias": "Margarita",
+ "id": 1,
+ "employment": [
+ {
+ "organizationName": "Codetechno",
+ "start-date": "2006-08-06"
+ },
+ {
+ "end-date": "2010-01-26",
+ "organizationName": "geomedia",
+ "start-date": "2010-06-17"
+ }
+ ]
+ }
+ }, {
+ "user": {
+ "userSince": "2011-01-22T10:10:00.000Z",
+ "friendIds": [
+ 1,
+ 4
+ ],
+ "name": "IsbelDull",
+ "nickname": "Izzy",
+ "alias": "Isbel",
+ "id": 2,
+ "employment": [
+ {
+ "organizationName": "Hexviafind",
+ "startDate": "2010-04-27"
+ }
+ ]
+ }
+ }, {
+ "user": {
+ "userSince": "2012-07-10T10:10:00.000Z",
+ "friendIds": [
+ 1,
+ 5,
+ 8,
+ 9
+ ],
+ "name": "EmoryUnk",
+ "alias": "Emory",
+ "id": 3,
+ "employment": [
+ {
+ "organizationName": "geomedia",
+ "endDate": "2010-01-26",
+ "startDate": "2010-06-17"
+ }
+ ]
+ }
+ } ]
### <a id="Select_distinct">SELECT DISTINCT</a>
SQL++'s `DISTINCT` keyword is used to eliminate duplicate items in results. The following example shows how it works.
@@ -152,11 +234,13 @@
This query returns:
- [
- { "foo": 1 },
- { "foo": 2 },
- { "foo": 3 }
- ]
+ [ {
+ "foo": 1
+ }, {
+ "foo": 2
+ }, {
+ "foo": 3
+ } ]
##### Example
@@ -164,7 +248,10 @@
This version of the query returns:
- [ 1, 2, 3 ]
+ [ 1
+ , 2
+ , 3
+ ]
### <a id="Unnamed_projections">Unnamed projections</a>
Similar to standard SQL, SQL++ supports unnamed projections (a.k.a, unnamed `SELECT` clause items), for which names are generated.
@@ -182,9 +269,10 @@
This query outputs:
- [
- { "$1": "Stoddard", "alias": "Margarita" }
- ]
+ [ {
+ "alias": "Margarita",
+ "$1": "Stoddard"
+ } ]
In the result, `$1` is the generated name for `substr(user.name, 1)`, while `alias` is the generated name for `user.alias`.
@@ -199,9 +287,10 @@
Outputs:
- [
- { "lname": "Stoddard", "alias": "Margarita" }
- ]
+ [ {
+ "lname": "Stoddard",
+ "alias": "Margarita"
+ } ]
## <a id="Unnest_clauses">UNNEST Clause</a>
For each of its input tuples, the `UNNEST` clause flattens a collection-valued expression into individual items, producing multiple tuples, each of which is one of the expression's original input tuples augmented with a flattened item from its collection.
@@ -218,10 +307,13 @@
This query returns:
- [
- { "userId": 1, "orgName": "Codetechno" },
- { "userId": 1, "orgName": "geomedia" }
- ]
+ [ {
+ "orgName": "Codetechno",
+ "userId": 1
+ }, {
+ "orgName": "geomedia",
+ "userId": 1
+ } ]
Note that `UNNEST` has SQL's inner join semantics --- that is, if a user has no employment history, no tuple corresponding to that user will be emitted in the result.
@@ -237,9 +329,9 @@
Returns:
- [
- { "userId": 1 }
- ]
+ [ {
+ "userId": 1
+ } ]
Note that if `u.hobbies` is an empty collection or leads to a `MISSING` (as above) or `NULL` value for a given input tuple, there is no corresponding binding value for variable `h` for an input tuple. A `MISSING` value will be generated for `h` so that the input tuple can still be propagated.
@@ -256,15 +348,28 @@
This returns:
- [
- { "uname": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" },
- { "uname": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" },
- { "uname": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" },
- { "uname": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" },
- { "uname": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" },
- { "uname": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" },
- { "uname": "IsbelDull", "message": " like samsung the plan is amazing" }
- ]
+ [ {
+ "uname": "MargaritaStoddard",
+ "message": " can't stand at&t its plan is terrible"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " dislike iphone its touch-screen is horrible"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " can't stand at&t the network is horrible:("
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " like verizon the 3G is awesome:)"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " can't stand motorola the touch-screen is terrible"
+ }, {
+ "uname": "IsbelDull",
+ "message": " like t-mobile its platform is mind-blowing"
+ }, {
+ "uname": "IsbelDull",
+ "message": " like samsung the plan is amazing"
+ } ]
Similarly, the above query can also be expressed as the `UNNEST`ing of a correlated SQL++ subquery:
@@ -345,15 +450,28 @@
Returns:
- [
- { "name": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" },
- { "name": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" },
- { "name": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" },
- { "name": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" },
- { "name": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" },
- { "name": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" },
- { "name": "IsbelDull", "message": " like samsung the plan is amazing" }
- ]
+ [ {
+ "name": "MargaritaStoddard",
+ "message": " like verizon the 3G is awesome:)"
+ }, {
+ "name": "MargaritaStoddard",
+ "message": " can't stand motorola the touch-screen is terrible"
+ }, {
+ "name": "MargaritaStoddard",
+ "message": " can't stand at&t its plan is terrible"
+ }, {
+ "name": "MargaritaStoddard",
+ "message": " dislike iphone its touch-screen is horrible"
+ }, {
+ "name": "MargaritaStoddard",
+ "message": " can't stand at&t the network is horrible:("
+ }, {
+ "name": "IsbelDull",
+ "message": " like samsung the plan is amazing"
+ }, {
+ "name": "IsbelDull",
+ "message": " like t-mobile its platform is mind-blowing"
+ } ]
##### Example
@@ -367,11 +485,8 @@
Returns:
- Error: Need an alias for the enclosed expression:
- (select element $GleambookMessages
- from $GleambookMessages as $GleambookMessages
- where ($GleambookMessages.authorId = $GleambookUsers.id)
- )
+ Error: "Syntax error: Need an alias for the enclosed expression:\n(select element GleambookMessages\n from GleambookMessages as GleambookMessages\n where (GleambookMessages.authorId = GleambookUsers.id)\n )",
+ "query_from_user": "use TinySocial;\n\nSELECT GleambookUsers.name, GleambookMessages.message\n FROM GleambookUsers,\n (\n SELECT VALUE GleambookMessages\n FROM GleambookMessages\n WHERE GleambookMessages.authorId = GleambookUsers.id\n );"
## <a id="Join_clauses">JOIN clauses</a>
The join clause in SQL++ supports both inner joins and left outer joins from standard SQL.
@@ -392,16 +507,30 @@
Returns:
- [
- { "uname": "MargaritaStoddard", "message": " can't stand at&t its plan is terrible" },
- { "uname": "MargaritaStoddard", "message": " dislike iphone its touch-screen is horrible" },
- { "uname": "MargaritaStoddard", "message": " can't stand at&t the network is horrible:(" },
- { "uname": "MargaritaStoddard", "message": " like verizon the 3G is awesome:)" },
- { "uname": "MargaritaStoddard", "message": " can't stand motorola the touch-screen is terrible" },
- { "uname": "IsbelDull", "message": " like t-mobile its platform is mind-blowing" },
- { "uname": "IsbelDull", "message": " like samsung the plan is amazing" },
- { "uname": "EmoryUnk" }
- ]
+ [ {
+ "uname": "MargaritaStoddard",
+ "message": " like verizon the 3G is awesome:)"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " can't stand motorola the touch-screen is terrible"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " can't stand at&t its plan is terrible"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " dislike iphone its touch-screen is horrible"
+ }, {
+ "uname": "MargaritaStoddard",
+ "message": " can't stand at&t the network is horrible:("
+ }, {
+ "uname": "IsbelDull",
+ "message": " like samsung the plan is amazing"
+ }, {
+ "uname": "IsbelDull",
+ "message": " like t-mobile its platform is mind-blowing"
+ }, {
+ "uname": "EmoryUnk"
+ } ]
For non-matching left-side tuples, SQL++ produces `MISSING` values for the right-side binding variables; that is why the last record in the above result doesn't have a `message` field. Note that this is slightly different from standard SQL, which instead would fill in `NULL` values for the right-side fields. The reason for this difference is that, for non-matches in its join results, SQL++ views fields from the right-side as being "not there" (a.k.a. `MISSING`) instead of as being "there but unknown" (i.e., `NULL`).
@@ -434,15 +563,99 @@
This first example query returns:
- [
- { "uid": 1, "msgs": [ { "msg": { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } },
- { "msg": { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" } },
- { "msg": { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" } },
- { "msg": { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" } },
- { "msg": { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } } ] },
- { "uid": 2, "msgs": [ { "msg": { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } },
- { "msg": { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } } ] }
- ]
+ [ {
+ "msgs": [
+ {
+ "msg": {
+ "senderLocation": [
+ 38.97,
+ 77.49
+ ],
+ "inResponseTo": 1,
+ "messageId": 11,
+ "authorId": 1,
+ "message": " can't stand at&t its plan is terrible"
+ }
+ },
+ {
+ "msg": {
+ "senderLocation": [
+ 41.66,
+ 80.87
+ ],
+ "inResponseTo": 4,
+ "messageId": 2,
+ "authorId": 1,
+ "message": " dislike iphone its touch-screen is horrible"
+ }
+ },
+ {
+ "msg": {
+ "senderLocation": [
+ 37.73,
+ 97.04
+ ],
+ "inResponseTo": 2,
+ "messageId": 4,
+ "authorId": 1,
+ "message": " can't stand at&t the network is horrible:("
+ }
+ },
+ {
+ "msg": {
+ "senderLocation": [
+ 40.33,
+ 80.87
+ ],
+ "inResponseTo": 11,
+ "messageId": 8,
+ "authorId": 1,
+ "message": " like verizon the 3G is awesome:)"
+ }
+ },
+ {
+ "msg": {
+ "senderLocation": [
+ 42.5,
+ 70.01
+ ],
+ "inResponseTo": 12,
+ "messageId": 10,
+ "authorId": 1,
+ "message": " can't stand motorola the touch-screen is terrible"
+ }
+ }
+ ],
+ "uid": 1
+ }, {
+ "msgs": [
+ {
+ "msg": {
+ "senderLocation": [
+ 31.5,
+ 75.56
+ ],
+ "inResponseTo": 1,
+ "messageId": 6,
+ "authorId": 2,
+ "message": " like t-mobile its platform is mind-blowing"
+ }
+ },
+ {
+ "msg": {
+ "senderLocation": [
+ 48.09,
+ 81.01
+ ],
+ "inResponseTo": 4,
+ "messageId": 3,
+ "authorId": 2,
+ "message": " like samsung the plan is amazing"
+ }
+ }
+ ],
+ "uid": 2
+ } ]
As we can see from the above query result, each group in the example query's output has an associated group
variable value called `msgs` that appears in the `SELECT *`'s result.
@@ -464,13 +677,85 @@
This variant of the example query returns:
- { "uid": 1, "msgs": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" },
- { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" },
- { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" },
- { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" },
- { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } ] },
- { "uid": 2, "msgs": [ { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" },
- { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } ] }
+ [ {
+ "msgs": [
+ {
+ "senderLocation": [
+ 38.97,
+ 77.49
+ ],
+ "inResponseTo": 1,
+ "messageId": 11,
+ "authorId": 1,
+ "message": " can't stand at&t its plan is terrible"
+ },
+ {
+ "senderLocation": [
+ 41.66,
+ 80.87
+ ],
+ "inResponseTo": 4,
+ "messageId": 2,
+ "authorId": 1,
+ "message": " dislike iphone its touch-screen is horrible"
+ },
+ {
+ "senderLocation": [
+ 37.73,
+ 97.04
+ ],
+ "inResponseTo": 2,
+ "messageId": 4,
+ "authorId": 1,
+ "message": " can't stand at&t the network is horrible:("
+ },
+ {
+ "senderLocation": [
+ 40.33,
+ 80.87
+ ],
+ "inResponseTo": 11,
+ "messageId": 8,
+ "authorId": 1,
+ "message": " like verizon the 3G is awesome:)"
+ },
+ {
+ "senderLocation": [
+ 42.5,
+ 70.01
+ ],
+ "inResponseTo": 12,
+ "messageId": 10,
+ "authorId": 1,
+ "message": " can't stand motorola the touch-screen is terrible"
+ }
+ ],
+ "uid": 1
+ }, {
+ "msgs": [
+ {
+ "senderLocation": [
+ 31.5,
+ 75.56
+ ],
+ "inResponseTo": 1,
+ "messageId": 6,
+ "authorId": 2,
+ "message": " like t-mobile its platform is mind-blowing"
+ },
+ {
+ "senderLocation": [
+ 48.09,
+ 81.01
+ ],
+ "inResponseTo": 4,
+ "messageId": 3,
+ "authorId": 2,
+ "message": " like samsung the plan is amazing"
+ }
+ ],
+ "uid": 2
+ } ]
Because this is a fairly common case, a third variant with output identical to the second variant is also possible:
@@ -500,11 +785,45 @@
This example query returns:
- [
- { "uid": 1, "msgs": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } ] },
- { "uid": 2, "msgs": [ { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" },
- { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } ] }
- ]
+ [ {
+ "msgs": [
+ {
+ "senderLocation": [
+ 40.33,
+ 80.87
+ ],
+ "inResponseTo": 11,
+ "messageId": 8,
+ "authorId": 1,
+ "message": " like verizon the 3G is awesome:)"
+ }
+ ],
+ "uid": 1
+ }, {
+ "msgs": [
+ {
+ "senderLocation": [
+ 48.09,
+ 81.01
+ ],
+ "inResponseTo": 4,
+ "messageId": 3,
+ "authorId": 2,
+ "message": " like samsung the plan is amazing"
+ },
+ {
+ "senderLocation": [
+ 31.5,
+ 75.56
+ ],
+ "inResponseTo": 1,
+ "messageId": 6,
+ "authorId": 2,
+ "message": " like t-mobile its platform is mind-blowing"
+ }
+ ],
+ "uid": 2
+ } ]
### <a id="Implicit_group_key_variables">Implicit grouping key variables</a>
In the SQL++ syntax, providing named binding variables for `GROUP BY` key expressions is optional.
@@ -530,11 +849,45 @@
This query returns:
- [
- { "authorId": 1, "msgs": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" } ] },
- { "authorId": 2, "msgs": [ { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" },
- { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" } ] }
- ]
+ [ {
+ "msgs": [
+ {
+ "senderLocation": [
+ 40.33,
+ 80.87
+ ],
+ "inResponseTo": 11,
+ "messageId": 8,
+ "authorId": 1,
+ "message": " like verizon the 3G is awesome:)"
+ }
+ ],
+ "authorId": 1
+ }, {
+ "msgs": [
+ {
+ "senderLocation": [
+ 48.09,
+ 81.01
+ ],
+ "inResponseTo": 4,
+ "messageId": 3,
+ "authorId": 2,
+ "message": " like samsung the plan is amazing"
+ },
+ {
+ "senderLocation": [
+ 31.5,
+ 75.56
+ ],
+ "inResponseTo": 1,
+ "messageId": 6,
+ "authorId": 2,
+ "message": " like t-mobile its platform is mind-blowing"
+ }
+ ],
+ "authorId": 2
+ } ]
Based on the three variable generation rules, the generated variable for the grouping key expression `message.authorId`
is `authorId` (which is how it is referred to in the example's `SELECT` clause).
@@ -559,11 +912,24 @@
This query returns:
- [
- { "uid": 1, "msgs": [ { "message": " like verizon the 3G is awesome:)" } ] },
- { "uid": 2, "msgs": [ { "message": " like samsung the plan is amazing" },
- { "message": " like t-mobile its platform is mind-blowing" } ] }
- ]
+ [ {
+ "msgs": [
+ {
+ "message": " like verizon the 3G is awesome:)"
+ }
+ ],
+ "uid": 1
+ }, {
+ "msgs": [
+ {
+ "message": " like samsung the plan is amazing"
+ },
+ {
+ "message": " like t-mobile its platform is mind-blowing"
+ }
+ ],
+ "uid": 2
+ } ]
Note that in the query above, in principle, `message` is not an in-scope variable in the `SELECT` clause.
However, the query above is a syntactically-sugared simplification of the following query and it is thus
@@ -627,10 +993,13 @@
This query returns:
- [
- { "uid": 1, "msgCnt": 5 },
- { "uid": 2, "msgCnt": 2 }
- ]
+ [ {
+ "uid": 1,
+ "msgCnt": 5
+ }, {
+ "uid": 2,
+ "msgCnt": 2
+ } ]
Notice how the query forms groups where each group involves a message author and their messages.
(SQL cannot do this because the grouped intermediate result is non-1NF in nature.)
@@ -675,10 +1044,13 @@
This query outputs:
- [
- { "authorId": 1, "$1": 5 },
- { "authorId": 2, "$1": 2 }
- ]
+ [ {
+ "authorId": 1,
+ "$1": 5
+ }, {
+ "authorId": 2,
+ "$1": 2
+ } ]
In principle, a `msg` reference in the query's `SELECT` clause would be "sugarized" as a collection
(as described in [Implicit group variables](#Implicit_group_variables)).
@@ -701,10 +1073,13 @@
This query returns:
- [
- { "aid": 1, "$1": 5 },
- { "aid": 2, "$1": 2 }
- ]
+ [ {
+ "$1": 5,
+ "aid": 1
+ }, {
+ "$1": 2,
+ "aid": 2
+ } ]
## <a id="Where_having_clauses">WHERE clauses and HAVING clauses</a>
Both `WHERE` clauses and `HAVING` clauses are used to filter input data based on a condition expression.
@@ -725,11 +1100,65 @@
This query returns:
- [
- { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" },
- { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] }
- { "id": 2, "alias": "Isbel", "name": "IsbelDull", "userSince": datetime("2011-01-22T10:10:00.000Z"), "friendIds": {{ 1, 4 }}, "employment": [ { "organizationName": "Hexviafind", "startDate": date("2010-04-27") } ], "nickname": "Izzy" }
- ]
+ [ {
+ "userSince": "2012-08-20T10:10:00.000Z",
+ "friendIds": [
+ 2,
+ 3,
+ 6,
+ 10
+ ],
+ "gender": "F",
+ "name": "MargaritaStoddard",
+ "nickname": "Mags",
+ "alias": "Margarita",
+ "id": 1,
+ "employment": [
+ {
+ "organizationName": "Codetechno",
+ "start-date": "2006-08-06"
+ },
+ {
+ "end-date": "2010-01-26",
+ "organizationName": "geomedia",
+ "start-date": "2010-06-17"
+ }
+ ]
+ }, {
+ "userSince": "2012-07-10T10:10:00.000Z",
+ "friendIds": [
+ 1,
+ 5,
+ 8,
+ 9
+ ],
+ "name": "EmoryUnk",
+ "alias": "Emory",
+ "id": 3,
+ "employment": [
+ {
+ "organizationName": "geomedia",
+ "endDate": "2010-01-26",
+ "startDate": "2010-06-17"
+ }
+ ]
+ }, {
+ "userSince": "2011-01-22T10:10:00.000Z",
+ "friendIds": [
+ 1,
+ 4
+ ],
+ "name": "IsbelDull",
+ "nickname": "Izzy",
+ "alias": "Isbel",
+ "id": 2,
+ "employment": [
+ {
+ "organizationName": "Hexviafind",
+ "startDate": "2010-04-27"
+ }
+ ]
+ } ]
## <a id="Limit_clauses">LIMIT clauses</a>
The `LIMIT` clause is used to limit the result set to a specified constant size.
@@ -744,9 +1173,31 @@
This query returns:
- [
- { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" }
- ]
+ [ {
+ "userSince": "2012-08-20T10:10:00.000Z",
+ "friendIds": [
+ 2,
+ 3,
+ 6,
+ 10
+ ],
+ "gender": "F",
+ "name": "MargaritaStoddard",
+ "nickname": "Mags",
+ "alias": "Margarita",
+ "id": 1,
+ "employment": [
+ {
+ "organizationName": "Codetechno",
+ "start-date": "2006-08-06"
+ },
+ {
+ "end-date": "2010-01-26",
+ "organizationName": "geomedia",
+ "start-date": "2010-06-17"
+ }
+ ]
+ } ]
## <a id="With_clauses">WITH clauses</a>
As in standard SQL, `WITH` clauses are available to improve the modularity of a query.
@@ -764,10 +1215,49 @@
This query returns:
- [
- { "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "userSince": datetime("2012-08-20T10:10:00.000Z"), "friendIds": {{ 2, 3, 6, 10 }}, "employment": [ { "organizationName": "Codetechno", "startDate": date("2006-08-06") }, { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ], "nickname": "Mags", "gender": "F" },
- { "id": 3, "alias": "Emory", "name": "EmoryUnk", "userSince": datetime("2012-07-10T10:10:00.000Z"), "friendIds": {{ 1, 5, 8, 9 }}, "employment": [ { "organizationName": "geomedia", "startDate": date("2010-06-17"), "endDate": date("2010-01-26") } ] }
- ]
+ [ {
+ "userSince": "2012-08-20T10:10:00.000Z",
+ "friendIds": [
+ 2,
+ 3,
+ 6,
+ 10
+ ],
+ "gender": "F",
+ "name": "MargaritaStoddard",
+ "nickname": "Mags",
+ "alias": "Margarita",
+ "id": 1,
+ "employment": [
+ {
+ "organizationName": "Codetechno",
+ "start-date": "2006-08-06"
+ },
+ {
+ "end-date": "2010-01-26",
+ "organizationName": "geomedia",
+ "start-date": "2010-06-17"
+ }
+ ]
+ }, {
+ "userSince": "2012-07-10T10:10:00.000Z",
+ "friendIds": [
+ 1,
+ 5,
+ 8,
+ 9
+ ],
+ "name": "EmoryUnk",
+ "alias": "Emory",
+ "id": 3,
+ "employment": [
+ {
+ "organizationName": "geomedia",
+ "endDate": "2010-01-26",
+ "startDate": "2010-06-17"
+ }
+ ]
+ } ]
The query is equivalent to the following, more complex, inlined form of the query:
@@ -806,10 +1296,85 @@
This query lists `GleambookUsers` that have posted `GleambookMessages` and shows all authored messages for each listed user. It returns:
- [
- { "messages": [ { "messageId": 8, "authorId": 1, "inResponseTo": 11, "senderLocation": point("40.33,80.87"), "message": " like verizon the 3G is awesome:)" }, { "messageId": 10, "authorId": 1, "inResponseTo": 12, "senderLocation": point("42.5,70.01"), "message": " can't stand motorola the touch-screen is terrible" }, { "messageId": 11, "authorId": 1, "inResponseTo": 1, "senderLocation": point("38.97,77.49"), "message": " can't stand at&t its plan is terrible" }, { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" }, { "messageId": 4, "authorId": 1, "inResponseTo": 2, "senderLocation": point("37.73,97.04"), "message": " can't stand at&t the network is horrible:(" } ], "uname": "MargaritaStoddard" },
- { "messages": [ { "messageId": 6, "authorId": 2, "inResponseTo": 1, "senderLocation": point("31.5,75.56"), "message": " like t-mobile its platform is mind-blowing" }, { "messageId": 3, "authorId": 2, "inResponseTo": 4, "senderLocation": point("48.09,81.01"), "message": " like samsung the plan is amazing" } ], "uname": "IsbelDull" }
- ]
+ [ {
+ "uname": "MargaritaStoddard",
+ "messages": [
+ {
+ "senderLocation": [
+ 38.97,
+ 77.49
+ ],
+ "inResponseTo": 1,
+ "messageId": 11,
+ "authorId": 1,
+ "message": " can't stand at&t its plan is terrible"
+ },
+ {
+ "senderLocation": [
+ 41.66,
+ 80.87
+ ],
+ "inResponseTo": 4,
+ "messageId": 2,
+ "authorId": 1,
+ "message": " dislike iphone its touch-screen is horrible"
+ },
+ {
+ "senderLocation": [
+ 37.73,
+ 97.04
+ ],
+ "inResponseTo": 2,
+ "messageId": 4,
+ "authorId": 1,
+ "message": " can't stand at&t the network is horrible:("
+ },
+ {
+ "senderLocation": [
+ 40.33,
+ 80.87
+ ],
+ "inResponseTo": 11,
+ "messageId": 8,
+ "authorId": 1,
+ "message": " like verizon the 3G is awesome:)"
+ },
+ {
+ "senderLocation": [
+ 42.5,
+ 70.01
+ ],
+ "inResponseTo": 12,
+ "messageId": 10,
+ "authorId": 1,
+ "message": " can't stand motorola the touch-screen is terrible"
+ }
+ ]
+ }, {
+ "uname": "IsbelDull",
+ "messages": [
+ {
+ "senderLocation": [
+ 31.5,
+ 75.56
+ ],
+ "inResponseTo": 1,
+ "messageId": 6,
+ "authorId": 2,
+ "message": " like t-mobile its platform is mind-blowing"
+ },
+ {
+ "senderLocation": [
+ 48.09,
+ 81.01
+ ],
+ "inResponseTo": 4,
+ "messageId": 3,
+ "authorId": 2,
+ "message": " like samsung the plan is amazing"
+ }
+ ]
+ } ]
This query is equivalent to the following query that does not use the `LET` clause:
@@ -839,10 +1404,11 @@
This query returns:
[
- " like t-mobile its platform is mind-blowing",
- " like samsung the plan is amazing",
- { "uname": "IsbelDull" }
- ]
+ " like t-mobile its platform is mind-blowing"
+ , {
+ "uname": "IsbelDull"
+ }, " like samsung the plan is amazing"
+ ]
## <a id="Subqueries">Subqueries</a>
In SQL++, an arbitrary subquery can appear anywhere that an expression can appear.
@@ -865,10 +1431,26 @@
For our sample data set, this query returns:
- [
- { "uid": 1, "msgs": [ { "messageId": 2, "authorId": 1, "inResponseTo": 4, "senderLocation": point("41.66,80.87"), "message": " dislike iphone its touch-screen is horrible" } ] },
- { "uid": 2, "msgs": [ ] }
- ]
+ [ {
+ "msgs": [
+ {
+ "senderLocation": [
+ 41.66,
+ 80.87
+ ],
+ "inResponseTo": 4,
+ "messageId": 2,
+ "authorId": 1,
+ "message": " dislike iphone its touch-screen is horrible"
+ }
+ ],
+ "uid": 1
+ }, {
+ "msgs": [
+
+ ],
+ "uid": 2
+ } ]
Note that a subquery, like a top-level `SELECT` statment, always returns a collection -- regardless of where
within a query the subquery occurs -- and again, its result is never automatically cast into a scalar.