Page Comparison

String

CONCAT

1..* String or column with type string

all

select concat('Cuisine ', c.Restaurant.cuisine) from Restaurants as c

Return combination of all strings

String

LOWER, UPPER

1 String or column with type string

all

select concat('Cuisine ', upper(c.Restaurant.cuisine)) from Restaurants as c

lowercase/uppercase of string

String

SUBSTRING

3 String or column with type string, index (integer), length (integer)

all

select concat('Cuisine ', substring(c.Restaurant.cuisine,2,3)) from Restaurants as c

substring of string

String

LENGTH

1 String or column with type string

all

select length(c.Restaurant.cuisine) from Restaurants as c

length of string

String

TRIM

1 String or column with type string

all

SELECT trim(' aaa '), trim(c.Restaurant.borough) FROM Restaurants AS c LIMIT 300

trim a string

DateTime

DATE_PART

2 part type ("month","year","dayOfMonth","hour","minute","second",
"millisecond","dayOfYear","dayOfWeek","week"), timestamp or field with timestamp type

all

SELECT date_part('year',timestamp) as year, date_part('month',timestamp) as month FROM QA_DETAIL AS c LIMIT 3

SELECT date_part('dayOfMonth',timestamp) as day, count(*) FROM QA_DETAIL AS c group by day

get date part from timestamp

DateTime

TO_TIMESTAMP/TIMESTAMP

1 timestamp or field with timestamp type

all

SELECT timestamp FROM QA_DETAIL AS c where timestamp > to_timestamp('2018-10-24T23:00:00.000Z') LIMIT 3

SELECT timestamp FROM QA_DETAIL AS c where timestamp > timestamp('2018-10-24T23:00:00.000Z') LIMIT 3

convert string to timestamp

Arrays

UNZIP

2 array type field, boolean (preserve null and empty arrays in unzip)

project

select unzip(Restaurant.grades,true) from Restaurants

unzip array in multiple instances. Second param enable/disable preserve null and empty arrays. This function can only be used alone in project because of the changing of the number of records. All filters applied are before unzip. You can use this in a subquery for continue operating over array

Arrays

ELEMAT

1 array type field

all

select elemat(Restaurant.grades,1) from Restaurants

get element by position in array

Geo

GEOWITHIN

2 geometry point field, geometry

where

select * from Supermarkets where geoWithin(Supermarkets.geometry, '{type : "Polygon" , coordinates: [ [ [-15.44488, 28.137924], [-15.423848, 28.137924], [-15.423848, 28.144054], [-15.44488, 28.144054], [-15.44488, 28.137924] ] ] }')

find some geometry field point in geometry structure as the second arguments. Only in where because mongoDB limitation

Aggregation

COUNT

1 field or *

project or having

select count(*) from Supermarkets

count

Aggregation

SUM, MIN, MAX, AVG, FIRST, LAST

1 field (type depending on operation)

project or having

select min(c.Supermarkets.status) as maxs from Supermarkets as c

aggregation functions. Last and first get first or last of a group

Conversion

TOINT, TOBOOL, TOSTRING, TOOBJECTID (or OID), TODOUBLE,TOLONG, TODATE, TODECIMAL

1 field (type depending on operation)

all

select concat('Cuisine ', UPPER(c.Restaurant.cuisine), ' of length ',toString(length(UPPER(c.Restaurant.cuisine)))) as complex from Restaurants as c

conversion between type

Timeserie

UNZIPTS

2 window type in String (uppercase), window frequency in String (uppercase)

project

SELECT unzipts('DAYS','MINUTES') FROM timeserie AS c LIMIT 3

unzip timeserie into plain strcture returning the instances like when they're inserted. This function can only be used alone in project because of the changing of the number of records. All filters applied are before unzipts. You can use this in a subquery for continue operating over timeserie