Querying Maps with SQL

You can use SQL to run optimized distributed queries on the keys and values of maps in a local cluster.

Querying Primitive Entries

A map entry consists of a key and a value. You can query the key through the __key field and the value through the this field.

For example, if you have a map named capital_cities, you can query that map, using the following SELECT statement.

sql

SELECT * FROM capital_cities;

+--------------------+------------+
|     __key          |    this    |
+--------------------+------------+
|        1           |   Tokyo    |
+--------------------+------------+

The this alias is returned by a query only if the value does not contain an object. For example, if the capital_cities value was a serialized object that had a name and a country field, the query result would include those fields as columns and exclude the this column.

+--------------------+------------+--------------+
|     __key          |    name    |   country    |
+--------------------+------------+--------------+
|        1           |   Tokyo    |    Japan     |
+--------------------+------------+--------------+

Querying Objects in Map Entries

If you store objects in a map, you can query the objects' fields only if they are serialized using one of the following options.

note

You cannot query nested object fields with cyclic dependencies using SQL.

• Serializable - DataSerializable, and IdentifiedDataSerializable

  Only public fields and getters are available. A public field is available with its exact name. A getter is available with the JavaBean naming conventions.

• Portable

  The fields that are written in the Portable.writePortable() method are exposed using their given names. The fields that are written in the GenericRecordBuilder API are exposed using their given names.

• Compact

  The fields that are written in the CompactSerializer.write() method are exposed using their given names. The fields that are written in the GenericRecordBuilder API are exposed using their given names.

  note

  If the compact serializer is not configured, only public fields and getters are available. A public field is available with its exact name. A getter is available with the JavaBean naming conventions.

• JSON.

Consider a map that stores Employee objects as values with name and age fields:

java

IMap<Long, Employee>

The SQL service has access to the Employee object's fields.

Name	Type
name	VARCHAR
age	INT

Together with the key, you may query those fields.

sql

SELECT __key, this, name, age FROM employee

If both the key and value have fields with the same name, you may only query the key's field and not the value's field, or you must rename the column when creating the mapping.

Querying Enums in Java

An enum is a user-defined type, which is mapped to the OBJECT type in SQL.

If you use Java, you can use the following options to query enums:

• Use a parameterized query.
• Create getter/setter methods that use the enum's ordinal or name.

For example, consider a map whose values are Employee objects with the following fields.

Name	Type
name	String
age	Int
status	EmployeeStatus

And the status field is an enum called EmployeeStatus.

java

public enum EmployeeStatus {
  PENDING,
  ACTIVE,
  VACATION,
  DELETED;
}

To query employees who have a VACATION status, you could do either of the following:

• Define a getter method that returns the string representation of the enum.

  java

  public enum EmployeeStatus {
    PENDING,
    ACTIVE,
    VACATION,
    DELETED;
  
    public String getEmployeeStatusString() {
      return employeeStatus != null ? employeeStatus.name() : null;
    }
  
    public void setEmployeeStatusString(String newValue) {
      employeeStatus = newValue == null ? null : EmployeeStatus.valueOf(newValue);
    }
  }

  sql

  SELECT * FROM employees
  WHERE employeeStatusString='VACATION';

• Use a parameterized query with Java.

  java

  sql.execute("SELECT * FROM employees WHERE employee.status = ?", EmployeeStatus.VACATION);

Streaming Map Changes

At the moment, you cannot run queries streaming the changes on maps with SQL.

Until this feature is available, use the Jet API to run streaming queries on maps. For a tutorial, see Subscribe to Changes to a Map.

Learn More

Learn the details of all the available SQL statements.

See more examples of the SELECT statement.