Hazelcast sql query with JSON_QUERY and filter

Question

I try to do a SQL query on Hazelcast map. ata in the map is in Json format.

How can I do a query with json filter ? I need to get all data with hasEvents == true

I tried to do this filter :

select JSON_QUERY(this, '$.data.field1.hasEvents?==true') from products

but it doesn't work

I expect to get all the entries in the map where data.field1.hasEvents = true

Answer 1

Assume that you have this table structure

CREATE TABLE IF NOT EXISTS product (id INT NOT NULL, data VARCHAR(100), PRIMARY KEY (id));
INSERT INTO product (id,data) VALUES (1, '{
  "field1": {
    "hasEvents": true
  }
}');

INSERT INTO product (id,data) VALUES (2, '{
  "field1": {
    "hasEvents": false
  }
}');

Your SQL should be something like

SELECT * FROM product WHERE JSON_QUERY(data, '$.field1.hasEvents') = 'true'

Answer 2

I am using hz 5.4.0-SNAPSHOT in this example. Link to the complete example is here https://github.com/OrcunColak/hzjetclient/blob/c8b526d0160ab3a2eb576716e314a262898feef4/src/main/java/com/colak/jet/sql/json/json_query/JsonQueryIMapTest.java

This is the test skeleton

public static final String MAP_NAME = "product";

public static void main(String[] args) {
  log.info("Starting HZ Server");

  // Start server
  HazelcastInstance hazelcastInstance = getHazelcastServerInstance();
  populateMap(hazelcastInstance );
  createMapping(hazelcastInstance );
  testSelect(hazelcastInstance );
  hazelcastInstance .shutdown();
  log.info("Test completed");
}

This method creates an embedded hz server

HazelcastInstance getHazelcastServerInstance() {
  Config config = new Config();

  // Add JetConfig
  JetConfig jetConfig = config.getJetConfig();
  jetConfig.setEnabled(true);
  jetConfig.setResourceUploadEnabled(true);
  return Hazelcast.newHazelcastInstance(config);
}

Let's populate the map

void populateMap(HazelcastInstance hazelcastInstance) {
  IMap<String, HazelcastJsonValue> map = hazelcastInstance .getMap(MAP_NAME);
  map.put("1", new HazelcastJsonValue("""
                {
                  "field1": {
                    "hasEvents": false
                  }
                }
                """));

  map.put("2", new HazelcastJsonValue("""
                {
                  "field1": {
                    "hasEvents": true
                  }
                }
                """));
}

This one creates a mapping. I have copied from your example

void createMapping(HazelcastInstance hazelcastInstance) {
  String createMappingQuery = format("CREATE OR REPLACE MAPPING %s Type IMap OPTIONS('keyFormat'='varchar', 'valueFormat'='json')",
                MAP_NAME);

  SqlService sqlService = hazelcastInstance.getSql();
  sqlService.executeUpdate(createMappingQuery);
}

Now select from the mapping that uses underlying IMap

void testSelect(HazelcastInstance hazelcastInstance) {
  SqlService sqlService = hazelcastInstance.getSql();
  try (SqlResult sqlResult = sqlService.execute("SELECT __key, this FROM product WHERE JSON_QUERY(this, '$.field1.hasEvents') = 'true'")) {
  if (sqlResult.isRowSet()) {
    String[] columnNames = getColumnNames(sqlResult);
    int numberOfColumns = columnNames.length;
    for (SqlRow sqlRow : sqlResult) {
      for (int columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
      Object columnValue = sqlRow.getObject(columnIndex);
      log.info("{} : {}", columnNames[columnIndex], columnValue);
      }
     }
   }
 }
}

I can see from the output that it selects the entry having key = 2