Nested Properties in Space and Document Objects

The SQL Query page shows how to express complex queries on flat Space entries (e.g. entries that are composed of scalar types like integers and strings), but in reality Space and Document entries are often composed of more complex types. For example, a Person class may have:

• An address property of type Address, which is a user-defined class encapsulating address details (e.g. city, street).

• A phoneNumbers property of type Map<String, String>, encapsulating various phone numbers (e.g. home, work, mobile).

• A groups property of type List<String>, encapsulating various groups the person belongs to.

An application would likely need to execute queries which involve such properties (e.g. a person which lives in city X, or is part of some group Y, or has a specific home phone number). This page explains how GigaSpaces SQLQuery can be used to express such queries.

Matching Paths

Matching a nested property is done by specifying a Path which describes how to obtain its value.

For example, suppose we have a Person class and an Address class, and Person has an address property of type Address:

@SpaceClass
public class Person {
    private Address address;
    // Getter and setter methods are omitted for brevity.
}

public class Address implements Serializable {
    private String city;
    // Getter and setter methods are omitted for brevity.
}

The following example queries for a Person with an address whose city equals "New York":

... = new SQLQuery<Person>(Person.class, "address.city = 'New York'");

The number of levels in the path is unlimited. For example, suppose the Address class has a Street class which encapsulates a name (String) and a houseNum (int). The following example queries for a Person who live on "*Main**" street:

... = new SQLQuery<Person>(Person.class, "address.street.name = 'Main'");

Naturally, any other feature of the SQL syntax can be used with paths:

// Using parameters instead of fixed criteria expressions:
... = new SQLQuery<Person>(Person.class, "address.city = ?", "New York");
// Using other SQL comparison operands:
... = new SQLQuery<Person>(Person.class, "address.street.houseNum >= 10");
// Using SQL composition operands to express compound predicates:
... = new SQLQuery<Person>(Person.class, "address.city = 'New York' AND address.street.houseNum >= 10");

Paths can also be specified in ORDER BY and GROUP BY clauses:

// Query for Persons in 'Main' street, sort results by city:
... = new SQLQuery<Person>(Person.class, "address.street.name = 'Main' ORDER BY address.city");

// Query for Persons in 'Main' street, group results by city:
... = new SQLQuery<Person>(Person.class, "address.street.name = 'Main' GROUP BY address.city");

Indexing

Indexing plays an important role in a system's architecture - a query without indexes can slow down the system significantly. Paths can be indexed to boost performance, similar to properties. For example, suppose we've analyzed our queries and decided that address.city is commonly used and should be indexed:

@SpaceClass
public class Person {
    private Address address;

    @SpaceIndex(path = "city")
    public Address getAddress() {
        return address;
    }
    // Setter method is omitted for brevity.
}

Nested Maps

The path syntax is tailored to detect Map objects and provide access to their content: when a property's key is appended to the path, it is implicitly resolved to the property's value (at runtime).

For example, suppose the Person class contains a phoneNumbers property which is a Map, encapsulating various phone numbers (e.g. home, work, mobile):

@SpaceClass
public class Person {
    private Map<String, String> phoneNumbers;
    // Getter and setter methods are omitted for brevity.
}

The following example queries for a Person whose phoneNumbers property contains the key-value pair "home" - "555-1234":

... = new SQLQuery<Person>(Person.class, "phoneNumbers.home = '555-1234'");

Indexing

Paths containing map keys can be indexed to boost performance, similar to "regular' paths. For example, suppose we analyzed our queries and decided that phoneNumbers.home is commonly used and should be indexed:

@SpaceClass
public class Person {
    private Address address;

    @SpaceIndex(path = "home")
    public Map<String, String> getPhoneNumbers() {
        return phoneNumbers;
    }
    // Setter method is omitted for brevity.
}

Nested Collections

The GigaSpaces SQL syntax supports a special operand [*], which is sometimes referred to as the "contains' operand. This operand is used in conjunction with collection properties to indicate that each collection item should be evaluated, and if at least one such item matches, the owner entry is considered as matched.

Suppose we have a type called Dealer with a property called cars (which is a list of strings). The following example queries for a Dealer whose cars collection property contains the "Honda" String:

... = new SQLQuery<Dealer>(Dealer.class, "cars[*] = 'Honda'");

For the same type and property, the following example queries for a Dealer whose cars collection property contains a string similar to "Honda":

... = new SQLQuery<Dealer>(Dealer.class, "cars[*] text:match 'Honda~'");

Now suppose that cars is not a collection of Strings but of a user-defined class called Car, which has a string property called company. The following example queries for a Dealer whose cars collection property contains a Car with company which equals "Honda":

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company = 'Honda'");

The String property can be queried for NULL or non NULL values. The property must not be indexed. The following example queries for a Dealer whose cars collection property contains a Car with a company which is null:

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company IS NULL");

or query for a Dealer whose cars collection property contains a Car with a company which is not null:

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company IS NOT NULL");

Matching collections within collections recursively is supported as well. Suppose the Car class has a collection of strings called tags, to store additional information. The following example queries for a Dealer which contains a car which contains a tag which equals "Convertible":

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].tags[*] = 'Convertible'");

Multiple Conditions On Collection Items

The scope of the [*] operand is bound to the predicate it participates in. When using it more than once, each occurrence is evaluated separately. This behavior is useful when looking for multiple distinct items, for example: a dealer with several cars of different companies. The following example queries for a Dealer which has both a Honda and a Subaru:

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company = 'Honda' AND cars[*].company = 'Subaru'");

You can use parentheses to specify multiple conditions on the same collection item. See examples below: The following example queries for a Dealer which has a Car whose company equals "Honda" and color equals "Red":

... = new SQLQuery<Dealer>(Dealer.class, "cars[*](company = 'Honda' AND color = 'Red')");

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company = 'Honda' AND cars[*].color = 'Red'");

The following example queries for a Dealer which has a Car whose company equals "Honda" and color equals "Red" and one of the Car nested Tags List equals to "Convertible":

... = new SqlQuery<Dealer>("Cars[*](company = 'Honda' AND color = 'Red' AND tags[*] = 'Convertible')");

Here is a graphical representation of this query:

... = new SqlQuery<Dealer>("Cars[*](company = 'Honda' OR color = 'Red' OR tags[*] = 'Convertible')");

In Operation

SQLQuery supports IN when used with nested properties. For example, to search for a dealer with cars where company is either "Honda" or "Ford"

... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company in ('Honda', 'Ford') ");

Indexing

Collection properties can be indexed to boost performance, similar to "regular' paths, except that each item in the collection is indexed. For example, suppose we analyzed our queries and decided that cars[*].company is commonly used and should be indexed:

@SpaceIndex(path = "[*].company")
public List<Car> getCars() {
  return this.cars;
}

Limitations

• The type of the nested object must be a class - querying interfaces is not supported.

• Nested properties' classes should be Serializable, otherwise the entry will not be accessible from remote clients.

• Arrays of primitive types (int, boolean, etc.) are not supported - use the equivalent wrapper type (java.lang.Integer, java.lang.Boolean, etc.) instead.

• "IS NULL' and "IS NOT NULL' are not supported for a property which has @SpaceIndex annotation, e.g. @SpaceIndex(path = "[*].company").

• "IS NULL' and "IS NOT NULL' are not supported for multiple conditions inside parentheses.