Arrays

Overview

Eloquera supports different types of arrays – multidimensional, jagged, complex and simple types.

Examples of supported arrays:

BasicUser[] users;

BasicUser[][][,,,,][] users;

long[][] values;

double[,,,] values;

Queries

Supported operations on arrays:

Operation	Query examples and description
[NOT] CONTAINS [ANY]	By default CONTAINS interprets as CONTAINS ANY, which returns true if any values from the right array contains in left array SELECT BasicUser WHERE Emails CONTAINS 'john@gmail.com' SELECT BasicUser WHERE Emails CONTAINS ('john@gmail.com', 'john@hotmail.com') Here (..) denotes array Parameters can be used to pass array Parameters param = db.CreateParameters(); param.AddList("emails", new string[]{"john@gmail.com", "david@hotmail.com"}); var res = db.ExecuteQuery("SELECT BasicUser WHERE Emails CONTAINS @emails", param);
[NOT] CONTAINS ALL	CONTAINS ALL return true if all values from the right array contains in left array SELECT BasicUser WHERE Emails CONTAINS ALL('john@gmail.com', 'john@hotmail.com')
[NOT] IN	IN can be treated as reverted CONTAINS ANY SELECT BasicUser WHERE ('john@gmail.com', 'john@hotmail.com') IN Emails
ALL	ALL requires that all elements of array shall satisfy the condition SELECT AdUser WHERE ALL AdPeriods > 10
ANY	ANY returns true if any of the elements of the array satisfy the condition SELECT AdUser WHERE ANY AdPeriods > 10
=	Arrays can be compared. ‘=’ operation returns true if: a) All elements of one array exist in another array. Order of values is ignored. SELECT AdUser WHERE AdPeriods = (10, 20, 30)
<> !=	‘<>’ (not equals) operation returns if two arrays have either a different length or values do not exist in each other SELECT AdUser WHERE AdPeriods != (10, 20, 30)
MIN(array)	MIN returns the minimum value from the array This query returns true, because the minimum value in the array is 10 SELECT AdUser WHERE MIN(AdPeriods) = 10
MAX(array)	MAX returns the maximum value from the array SELECT AdUser WHERE MAX(AdPeriods) = 30
AVERAGE(array)	AVERAGE returns the average value of all elements in the array SELECT AdUser WHERE AVERAGE(AdPeriods) = 20
MEDIAN(array)	MEDIAN returns the median values of all elements in the array SELECT AdUser WHERE MEDIAN(AdPeriods) = 20
SUM(array)	SUM returns the sum of all values in the array SELECT AdUser WHERE SUM(AdPeriods) = 60
LEN(array)	LEN returns the length of the array SELECT AdUser WHERE LEN(AdPeriods) = 3

Arrays (…) magic

Arrays can be constructed using parameters, fields, complex and simple arrays and standalone values.

For example:

Parameter param = db.CreateParameters();

param.AddList("array", new int[]{1, 2, 3, 4});

var res = db.ExecuteQuery("SELECT AdUser WHERE ALL (10, 20, 30) IN (AdPeriods, 10 + 20, @array, min(10, 2))");

Query engine creates a list, as follows:

(values from AdPeriods, 10 + 20 result, values from @array, min(10, 2) result )

Array fields access

Fields of all array types can be accessed as any other type, just name a field via a ‘.’ (dot)

For example:

var res = db.ExecuteQuery("SELECT BasicUser WHERE Friends.CurrentLocation.Country = 'Australia'");

Just type via “dot” name of the field in the type.

public class BasicUser

{

BasicUser[] Friends { get; set; }

Location CurrentLocation { get; set; }

// rest of the fields

//...

}

public class Location

{

string Country { get; set; }

// rest of the fields

//...

}

Note: The query syntax remains the same, irrespective of [] or [,,][].

Array of DateTime

You can use a DateTime array using a BETWEEN operation, as follows;

Parameters param = db.CreateParameters();

param["dt1"] = new DateTime(2009, 05, 02);

param["dt2"] = new DateTime(2009, 05, 04);

var res = db.ExecuteQuery("SELECT BasicUser WHERE BirthDate BETWEEN @dt1 AND @dt2", param);

Note: The query syntax remains the same, irrespective of DateTime, DateTime[] or DateTime[][,,].