Apache Geode

SELECT Statement

The SELECT statement allows you to filter data from the collection of object(s) returned by a WHERE search operation. The projection list is either specified as * or as a comma delimited list of expressions.

For *, the interim results of the WHERE clause are returned from the query.

Examples:

Query all objects from the region using *. Returns the Collection of portfolios (The exampleRegion contains Portfolio as values).

SELECT * FROM /exampleRegion

Query secIds from positions. Returns the Collection of secIds from the positions of active portfolios:

SELECT secId FROM /exampleRegion, positions.values TYPE Position 
WHERE status = 'active'

Returns a Collection of struct<type: String, positions: map> for the active portfolios. The second field of the struct is a Map ( jav.utils.Map ) object, which contains the positions map as the value:

SELECT "type", positions FROM /exampleRegion 
WHERE status = 'active'

Returns a Collection of struct<portfolios: Portfolio, values: Position> for the active portfolios:

SELECT * FROM /exampleRegion, positions.values 
TYPE Position WHERE status = 'active'

Returns a Collection of struct<pflo: Portfolio, posn: Position> for the active portfolios:

SELECT * FROM /exampleRegion portfolio, positions positions 
TYPE Position WHERE portfolio.status = 'active'

SELECT Statement Results

The result of a SELECT statement is either UNDEFINED or is a Collection that implements the SelectResults interface.

The SelectResults returned from the SELECT statement is either:

1. A collection of objects, returned for these two cases:

   • When only one expression is specified by the projection list and that expression is not explicitly specified using the fieldname:expression syntax
   • When the SELECT list is * and a single collection is specified in the FROM clause

2. A collection of Structs that contains the objects

When a struct is returned, the name of each field in the struct is determined following this order of preference:

1. If a field is specified explicitly using the fieldname:expression syntax, the fieldname is used.
2. If the SELECT projection list is * and an explicit iterator expression is used in the FROM clause, the iterator variable name is used as the field name.
3. If the field is associated with a region or attribute path, the last attribute name in the path is used.
4. If names cannot be decided based on these rules, arbitrary unique names are generated by the query processor.

DISTINCT

Use the DISTINCT keyword if you want to limit the results set to unique rows. Note that in the current version of Geode you are no longer required to use the DISTINCT keyword in your SELECT statement.

SELECT DISTINCT * FROM /exampleRegion

Note: If you are using DISTINCT queries, you must implement the equals and hashCode methods for the objects that you query.

LIMIT

You can use the LIMIT keyword at the end of the query string to limit the number of values returned.

For example, this query returns at most 10 values:

SELECT * FROM /exampleRegion LIMIT 10

ORDER BY

You can order your query results in ascending or descending order by using the ORDER BY clause. You must use DISTINCT when you write ORDER BY queries.

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID

The following query sorts the results in ascending order:

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID asc

The following query sorts the results in descending order:

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID desc

Note: If you are using ORDER BY queries, you must implement the equals and hashCode methods for the objects that you query.

Preset Query Functions

Geode provides several built-in functions for evaluating or filtering data returned from a query. They include the following:

ELEMENT(SELECT DISTINCT * 
 FROM /exampleRegion 
 WHERE id = 'XYZ-1').status = 'active'

IS_DEFINED(SELECT DISTINCT * 
FROM /exampleRegion p 
WHERE p.status = 'active')

SELECT DISTINCT * 
FROM /exampleRegion p 
WHERE IS_UNDEFINED(p.status)