Page History
...
All MongoSQL queries are SELECT statements of this form:
| Code Block |
|---|
SELECT [FLATTEN | JSON | FLATTEN_ARRAY] [DISTINCT|ALL] expr1 [[AS] alias1], expr2 [[AS] alias2], ... [FROM collection_name] [[AS] tablealias] [WHERE condition] [GROUP BY field1|alias1, field2|alias2, ...] [HAVING condition] [ORDER BY field1|alias1 [DESC|ASC], field2|alias2 [DESC|ASC], ...] [LIMIT n] [OFFSET o] GO SELECT ... { UNION | UNION ALL | INTERSECT | EXCEPT } SELECT ... GO |
...
SELECT clause
SELECT expr1 [AS alias1], expr2 [AS alias2], ...
...
The SELECT clause supports an AS section, which defines an alias for the field, literal, or functional expression.
Example
| Code Block |
|---|
SELECT city AS htowns
FROM zips
WHERE city LIKE 'H%' |
...
Subdocument and Array Fields
Querying subdocument and array data is supported automatically. Consider the following:
| Code Block |
|---|
Schema
----------------------------------------------------
|- _id: (ObjectId)
|- teamName: (string)
|- city: (string)
|- division: (string)
|- ranking: (integer)
+- managerName: (record)
| |- first: (string)
| |- last: (string)
|- colors: (array[string])
|- worldChampionships: (integer)
+- stats: (array[record])
| |- year: (integer)
| |- wins: (integer)
| |- losses: (integer)
| |- winPercentage: (double) |
...
|
Notice that we have subdocument and array fields. If you run the following query:
| Code Block |
|---|
SELECT FLATTEN_ARRAY teamName, managerName.first, colors, stats.year, stats.wins
FROM baseball |
The query would return data with a flattened output like the following:
| Code Block |
|---|
...
teamName managerName.first colors stats.year stats.wins
----------- -------------------- --------- ------------- -------------
Cubs Dale blue 2010 75
Cubs Dale white 2010 75
Cubs Dale blue 2011 71
Cubs Dale white 2011 71
Cubs Dale blue 2012 61
Cubs Dale white 2012 61
Cardinals Mike red 2010 86
Cardinals Mike white 2010 86
Cardinals Mike red 2011 90
Cardinals Mike white 2011 90
Cardinals Mike red 2012 88
Cardinals Mike white 2012 88
Yankees Joe navy 2010 95
Yankees Joe white 2010 95
Yankees Joe navy 2011 97
Yankees Joe white 2011 97
Yankees Joe navy 2012 95
Yankees Joe white 2012 95
Mets Terry blue 2010 79
Mets Terry white 2010 79
Mets Terry blue 2011 77
Mets Terry white 2011 77
Mets Terry blue 2012 74
Mets Terry white 2012 74
Giants Bruce orange 2010 92
Giants Bruce black 2010 92
Giants Bruce orange 2011 86
Giants Bruce black 2011 86
Giants Bruce orange 2012 94
Giants Bruce black 2012 94
Orioles Buck orange 2010 66
Orioles Buck black 2010 66
Orioles Buck white 2010 66
Orioles Buck orange 2011 69
Orioles Buck black 2011 69
Orioles Buck white 2011 69
Orioles Buck orange 2012 93
Orioles Buck black 2012 93
Orioles Buck white 2012 93
Angels Mike red 2010 80
Angels Mike white 2010 80
Angels Mike red 2011 86
Angels Mike white 2011 86
Angels Mike red 2012 89
Angels Mike white 2012 89
A's Bob green 2010 81
A's Bob gold 2010 81
A's Bob white 2010 81
A's Bob green 2011 74
A's Bob gold 2011 74
A's Bob white 2011 74
A's Bob green 2012 94
A's Bob gold 2012 94
A's Bob white 2012 94
Rays Joe blue 2010 96
Rays Joe white 2010 96
Rays Joe blue 2011 91
Rays Joe white 2011 91
Rays Joe blue 2012 90
Rays Joe white 2012 90
White Sox Robin black 2010 88
White Sox Robin silver 2010 88
White Sox Robin white 2010 88
White Sox Robin black 2011 79
White Sox Robin silver 2011 79
White Sox Robin white 2011 79
White Sox Robin black 2012 85
White Sox Robin silver 2012 85
White Sox Robin white 2012 85 |
...
FLATTEN | JSON | FLATTEN_ARRAY option
...
FLATTEN is default. FLATTEN removes nesting levels of subdocuments. FLATTEN does not flatten array fields. Each subdocument field is returned as a separate field in the result set.
Example
| Code Block |
|---|
SELECT FLATTEN *
FROM zips
WHERE city LIKE 'H%' |
...
JSON
JSON returns subdocuments and arrays in JSON format.
...