This is a prerelease version.

Querying Maps with SQL

With SQL you can query the keys and values of maps in your local cluster.

Querying Maps by Name

The SQL service exposes map objects as tables in the predefined partitioned schema using exact names. This schema is in the SQL service search path so that you can access the map objects with or without the schema name.

Schema names, table names, and field names are case-sensitive.

For example, you can access the employee map as employee or partitioned.employee, but not as Employee:

SELECT * FROM employee
SELECT * FROM partitioned.employee

Querying Map Entries by Field

The SQL service resolves fields in map entries automatically by reading the first local entry in the map. If the map does not have local entries on the member where the query is started, then the list of fields cannot be resolved, and an exception is thrown.

Because Hazelcast looks for one random entry in a map, you should make sure that all entries contain the same fields.

For example, in this JSON-valued map, Hazelcast may take the first entry at random and resolve the fields name and age :

key value

1

{"name":"Alice","age":42}

2

{"name":"Bob","age":43,"petName":"Zaz"}

If you try to query the petName field, it will have the value null for the entry with key=1. However, in future optimizations we may throw an error instead of returning null. Therefore, it’s best to make sure that all entries contain the same fields.

Querying Primitive Entries

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

For example, if you have an 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 an 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 Object Fields

If you store objects in a map, you can query its fields only if the object is serialized using one of the following options:

  • For Java objects (Serializable, DataSerializable, 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.

  • For Portable objects, the fields that are written in the Portable.writePortable() method are exposed using their exact names.

  • HazelcastJsonValue

You cannot query nested object fields using SQL.

Consider an Employee class where the SQL service can access the following fields:

Name

Type

name

VARCHAR

age

integer

Together with the key and value objects, you may query the following fields from IMap<Long, Employee>:

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.

Next Steps

Learn the details of all the available SQL statements.

See more examples of the SELECT statement.