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.

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' top-level fields only if they are serialized using one of the following options.

You cannot query nested object fields 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 (BETA)

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.

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:

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.

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.

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.

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);
  }
}

SELECT * FROM employees
WHERE employeeStatusString='VACATION';

Use a parameterized query with 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.