SQL Select Analytics

Retrieves documents from the database and enables the selection of one or many documents or attributes from one or many collections

Select executes 100% in database and utilizes database indexes when available. Select also utilizes sparse bit map indexes and can outperform commercial analytical SQL Engines in some cases

The best way to learn how select handles documents and joins is to run queries in the mongo shell and analyze the output

Syntax

Select command

Passing parameters

You can stringify the entire query without using parameters. Parameters are however more efficient in some cases such as querying against documents and binary types and so on

With - Useful flags

Array positional operators

The concept of array positional operators come from mongodb. Positional operators allow you to project a specific element in an array based on the query criteria specified. Nodechef supports two positioanl operators which are the single position projector ($) and the multi position projector ($*)

Single position projector ($)

This projector allows you to project a single element from an array using the query predicate specified. The query returns the first element in the given array that matches the predicate. Unlike mongodb, the Nodechef single position projector can project elements nested multiple levels in an array.

Multi position projector ($*)

This operator can be used to project multiple elements that match the query critera

Array index projection

To project an element at a specific index in an array. You can specify the index of the element using dot notation. It is important to avoid using numbers as attribute names for arrays in your documents as this can possibly cause unexepcted behaviours when querying the array by index.

Hierarchical joins

The arrayfromSelect and documentfromSelect functions shorthand "[]" and "{}" respectively can be used to perform hierarchical joins. These two functions accept a select statement as an input parameter. These functions can then be nested multiple levels to output a rich JSON document with input from multiple tables.

Example

The below query joins two collections to the NewsFeed collection to retrieve Comments associated with an entry in the newsFeed as well as the user details associated with the comment. Note only 20 entries in the NewsFeed collection are returned as well as only the first 10 comments per post are returned.

Select cursors

By default select returns a single batch of results, in some cases a cursor can be useful. To create a cursor, use the enablecursor flag. Note this cursor returned is the same returned from any mongodb aggregate query. The PHP driver supports this as a CommandCursor. However the node.js driver does not directly support this. Since you might not be able to hack the driver directly, For this cursor to be useful, MongoDB 3.2 introduces a new command getMore to make it easy to iterate over a command cursor like aggregate and Nodechef select.

You can repeatedly call this function to exhaust all the results in this cursor. By default SELECT returns 1024 documents when the limit clause is ommitted.

Example

Multi Select command

Executes one or more select statements either sequentially or in parallel.

In some cases, you might want to query several collections in a way which is not possible with a join. You can avoid the cost of multiple round trips to the database by sending multiple select statements in a single commands. Each select statement can be executed in parallel on the database if required.

Case

Operators

Use arithmetic + (Add) for string concatenation

Aggregate functions

• int64Count([DISTINCT] expression)
• inputAvg([DISTINCT] expression)
• inputMin([DISTINCT] expression)
• inputMax([DISTINCT] expression)
• inputSum([DISTINCT] expression)
• inputStdev([DISTINCT] expression)
• inputStdevp([DISTINCT] expression)
• inputVar([DISTINCT] expression)
• inputVarp([DISTINCT] expression)
• inputFirst(expression)
• inputLast(expression)
• arrayPush(expression) Returns an unordered array
• arrayAddToSet(expression) Returns an array of ordered elements.

String functions

• stringTrim(string, optional char)
• int32IndexOf(string search, string lookup, optional int32 start_index)
• int32Length(string)
• stringSubstring(string, int32 start_offset, optional int32 length)
• stringReplace(string search, string lookup, string replace)
• stringReverse(string)
• int32Hash(string)
• stringConcat(string, string, ... )
• stringTolower(string)
• stringToupper(string)
• arraySplit(int32 split_char, string)
• arraySplit(string split_sequence, string)
• stringLeft(string, int32 length)
• stringRight(string, int32 length)
• stringSoundex(string)
• stringDoubleMetaPhone(string)
• int32LastIndexOf(string search, string lookup, optional int32 start_index)
• int32IsNullOrEmpty(string)
• int32IsNullOrWhiteSpace(string)
• int32RegexMatch(string pattern, string regex_options, string input)
  Set regex_options to null or an emptry string if not applicable. Possible regex options include:

  • i (Ignore Case)
  • m (Multiline)
  • s (Singeline)
  • x (IgnorePatternWhitespace)
• stringRegexReplace(string pattern, string regex_options, string input, string replacement)
• stringRegexExtract(string pattern, string regex_options, string input)
• stringNumformat(string number)
  Input: 1000, Returns: 1,000
• stringStartsWith(string input, string match, optional int32 ignore_case)
• stringEndsWith(string input, string match, optional int32 ignore_case)
• floatJaroWinklerDistance(string, string, float threshold)
• floatLevensteinDistance(string, string)
• floatNGramDistance(string, string, int)
• stringGuid()

Math functions

Parameter number refers to any integer or floating point data type which includes int32, int64, float, double, decimal

input means the function returns a value of data type as that of the input parameter

• numberAbs(number)
• doubleAcos(number)
• doubleAsin(number)
• doubleAtan(number)
• doubleAtn2(double, double)
• inputCos(decimal / double)
• doubleCos(double)
• doubleCot(double)
• doubleDegrees(double)
• doubleExp(double)
• inputFloor(decimal / double)
• doubleLog(double, optional double)
• doubleLog10(double)
• doublePi()
• doublePower(double, double)
• doubleRadians(double)
• inputRound(decimal/double, optional int32)
• int32Sign(number)
• doubleSin(double)
• doubleSqrt(double)
• inputSquare(number)
• doubleTan(double)

Date functions

• datetimeNow()
• datetimeDateAdd(string date_part, int32 add_value, datetime)
  date_part: day, hour, minute, second, millisecond, month, year
• doubleDateDiff(string date_part, datetime, datetime)
• int32DatePart(string date_part, datetime)
  date_part: week, weekday, dayofyear, day, hour, minute, second, millisecond, month, year
• stringDateConvert(string format_str, datetime)
  eg: DateConvert('{yyyy}-{mm}-{dd} {hh}:{mm}:{ss}.{ms}', now());
  

  • hh - hour
  • mm - minute
  • ss - second
  • ms - millisecond
  • dd - day of month
  • yyyy - year (1999)
  • yy - year (99)
  • MM - month (01 - 12)
  • MS - month (Jan - Dec)
  • doy - day of year
  • ddd - day of week
  • dddd - day of week string

Array functions

• arrayArrayFromSelect(sql_select)
  SELECT post_tile, post_description, aggregate_likes_count, ArrayFromSelect(( SELECT comment_ts, comment, u.user, u.profile_pic FROM comments c INNER join users u on u._id = c._id WHERE c.post_id = p._id)) as post_comments FROM posts p WHERE post_id = 20

  Remarks

  If the select statement in the ArrayFromSelect returns multiple columns, this function will return an array of sub documents. However, if the select statement returned only a single column, the select statement will return an array with the value of the column from each row returned. A sub document is not created in this case.

  This function can be used to perform hierarchical joins.

  The select statement inside the ArrayFromSelect must be surrounded with brackets else a syntax error will be thrown.
• inputGetElement(element_index, array)
• int32Arraylength(array)
• arrayArrayslice(array, int32 offset, optional int32 length)
• arrayArrayMultiIntersect(array, array, ...)
• arrayArrayMultiUnion(array, array, ...)
• boolArrayIsSubset(array total_set, array sub_set)
• arrayArrayRelativeComplement(array seta, array setb)

Cast functions

• inputIsNull(check_expression, return_expression)
• int32ToInt32(expression)
• int64ToInt64(expression)
• floatToFloat(expression)
• doubleToDouble(expression)
• decimalToDecimal(expression)
  Decimal values must be converted to string first before it can be sent across the wire. BSON documents lack support for decimals.
• DatetimeToDatetime(expression)
• StringToString(expression)
• floatGeoPointX(geopoint_expression)
  If the parameter is an array, the first element is treated as Y (Longitude) and the second element is treated as X (Latitude).
  If the parameter is a BSON document, the "lng" attribute represents longitude (Y) and "lat" represents latitude (X).
• floatGeoPointY(geopoint_expression)

Geo search functions

Geo search functions can be used in combination with any other group of functions such as text, math etc. Nodechef uses spherical geometry for all operations except when the geojson object specified is a bounding box

• boolnearSphere([field], { $geometry : {}, $minDistance, $maxDistance })
  SELECT * FROM restaurants WHERE nearSphere([location], { $geometry : { type : "point", coordinates : [ -73.9667, 40.78 ] }, $minDistance: 1000, $maxDistance: 5000 }) AND menu_tags IN ('poutine', 'rotisserie chicken') # Square brackets surrounding field names are only for demonstration purposes.

  Remarks

  Reference the mongodb docs for a complete guide on how the nearSphere function works
  The $geometry specified must be a valid GeoJson object. if the $geometry specified is a point, the function behaves exactly like the mongodb $nearSphere operator. Unlike in mongodb, the NodeChef $nearSphere accepts other valid GeoJson objects.
• boolgeoWithin([field], { $geometry : { <geojson_geometry> } })
  SELECT * FROM restaurants WHERE geoWithin([location], { $geometry : { type : "Polygon", coordinates: [ [ [ 0, 0 ], [ 3, 6 ], [ 6, 1 ], [ 0, 0 ] ] ] }})

  Remarks

  Reference the mongodb docs for function behaviour
• boolgeoWithin([field], { $polygon : [] })
  Reference the mongodb docs for function behaviour
• boolgeoWithin([field], { $box : [] })
  Reference the mongodb docs for function behaviour
• boolgeoWithin([field], { $centerSphere: [ [ <x>, <y> ], <radius> ] })
  Reference the mongodb docs for function behaviour
• boolgeoIntersects([field], { $geometry : { <geojson_geometry> } })
  SELECT * FROM restaurants WHERE geoIntersects([location], { $geometry : { type: "Polygon" , coordinates: [ [ [ 0, 0 ], [ 3, 6 ], [ 6, 1 ], [ 0, 0 ] ] ] }})

  Remarks

  Reference the mongodb docs for function behaviour

Text functions

• booltextSearch({ <valid $textx document> }, [field])
  SELECT s.address, s.storeName, p.price, p.title, p.details FROM products p INNER join seller s on s._id = p.seller_id WHERE textsearch({ phrase : "civilization or barba", gap : 2, fuzzy : "T2_2" }, [title]) AND in_stock = 1 AND price <= 200

  Remarks

  For a complete reference on a $textx document, reference the search guide found here. You can use the textsearch function in combination with other groups of functions such as geo search, math etc. Unfortunately you cannot perform snipettiing using SQL Select. One must use the JSON style query from the search guide.